11g新特性 —— 索引不可见（Index Invisible）

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2009-08-26 06:42:40

分享到 

索引维护是DBA的一项重要工作。当一个系统运行很长一段时间，经过需求变更、结构设计变化后，系统中就可能会存在一些不会被使用的索引，或者使用效率很低的索引。这些索引的存在，不仅占用系统空间，而且会降低事务效率，增加系统的waits。因此，我们需要找出那些无用或低效索引的索引并删除它们（找出无用索引可以通过索引监控的方法）。     但是，直接删除索引还是存在一定风险的。例如，某些索引可能只是在一些周期的作业中被使用到，而如果监控周期没有覆盖到这些作业的触发点，就会认为索引是无用的而被删除。当作业启动后，可能就会对系统性能造成冲击。这时，可能就会手忙脚乱的去找回索引定义语句、重建索引。     11g之前，我们可以先不删除索引，而将其修改为unusable。这样的话，索引的定义并未删除，只是索引不能再被使用也不会随着表数据的更新而更新。当需要重新使用该索引时，需要用rebuild语句重建、然后更新统计信息。对于一些大表来说，这个时间可能就非常长。     在11g里，oracle提供了一个新的特性来降低直接删除索引或者禁用索引的风险，那就是索引不可见（Index Invisible）。我们可以在创建索引时指定invisible属性或者用alter语句来修改索引为invisible。 SQL代码 SQL> create index t_test1_idx on t_test1(table_name) invisible;       Index created.       SQL> alter index t_test1_idx invisible;       Index altered.       当索引被设为不可见后，实际上就是指该索引对于优化器不可见，而索引的正常更新并不受影响——即表在增、删、改时，索引也会被更新。只是当优化器在选择查询计划时会“无视”该索引（无论是CBO或RBO）： SQL代码 SQL> create table t_test1 as select * from dba_tables;       Table created.       SQL> create index t_test1_idx on t_test1(table_name);       Index created.       SQL> analyze table t_test1 compute statistics for table for all indexes;       Table analyzed.       SQL> set autot trace exp       SQL> select * from t_test1 where table_name like 'HR%';       no rows selected          Execution Plan    ----------------------------------------------------------    Plan hash value: 3466041839       -------------------------------------------------------------------------------------------    | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    -------------------------------------------------------------------------------------------    |   0 | SELECT STATEMENT            |             |   126 | 27468 |    14   (0)| 00:00:01 |    |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |   126 | 27468 |    14   (0)| 00:00:01 |    |*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX |    23 |       |     2   (0)| 00:00:01 |    -------------------------------------------------------------------------------------------       Predicate Information (identified by operation id):    ---------------------------------------------------          2 - access("TABLE_NAME" LIKE 'HR%')           filter("TABLE_NAME" LIKE 'HR%')       SQL> alter index t_test1_idx invisible;       Index altered.       SQL> select * from t_test1 where table_name like 'HR%';       no rows selected          Execution Plan    ----------------------------------------------------------    Plan hash value: 1883417357       -----------------------------------------------------------------------------    | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |    -----------------------------------------------------------------------------    |   0 | SELECT STATEMENT  |         |   126 | 27468 |    25   (0)| 00:00:01 |    |*  1 |  TABLE ACCESS FULL| T_TEST1 |   126 | 27468 |    25   (0)| 00:00:01 |    -----------------------------------------------------------------------------       Predicate Information (identified by operation id):    ---------------------------------------------------          1 - filter("TABLE_NAME" LIKE 'HR%')        如果我们需要重新使该所有有效，只需要再将其改为visible就可以了——这个过程不是重建，仅仅是修改索引的一个属性，非常快！ SQL代码 SQL> set timing on   SQL> alter index t_test1_idx visible;       Index altered.       Elapsed: 00:00:00.01       当然，当索引被设为不可见时，并非完全不可用。可以通过修改参数optimizer_use_invisible_indexes为true（默认为false，system级别和session级别都可以）： SQL代码 SQL> select index_name, visibility from user_indexes where index_name='T_TEST1_IDX';       INDEX_NAME                     VISIBILIT    ------------------------------ ---------    T_TEST1_IDX                    INVISIBLE       SQL>       SQL> alter session set optimizer_use_invisible_indexes=true;       Session altered.       SQL> set autot trace exp    SQL> select * from t_test1 where table_name like 'HR%';       Execution Plan    ----------------------------------------------------------    Plan hash value: 3466041839       -------------------------------------------------------------------------------------------    | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    -------------------------------------------------------------------------------------------    |   0 | SELECT STATEMENT            |             |   126 | 27468 |    14   (0)| 00:00:01 |    |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1     |   126 | 27468 |    14   (0)| 00:00:01 |    |*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX |    23 |       |     2   (0)| 00:00:01 |    -------------------------------------------------------------------------------------------       Predicate Information (identified by operation id):    ---------------------------------------------------          2 - access("TABLE_NAME" LIKE 'HR%')           filter("TABLE_NAME" LIKE 'HR%')       这里还需要指出一点，当索引不可见时，只能通过上面的参数使之在查询计划中可用，即使通过HINT也无法改变。 SQL代码 SQL> select /*+ index(t_test1 T_TEST1_IDX) */ * from t_test1 t where table_name like 'HR%';    Elapsed: 00:00:00.00       Execution Plan    ----------------------------------------------------------    Plan hash value: 1883417357       -----------------------------------------------------------------------------    | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |    -----------------------------------------------------------------------------    |   0 | SELECT STATEMENT  |         |   126 | 27468 |    25   (0)| 00:00:01 |    |*  1 |  TABLE ACCESS FULL| T_TEST1 |   126 | 27468 |    25   (0)| 00:00:01 |    -----------------------------------------------------------------------------       Predicate Information (identified by operation id):    ---------------------------------------------------          1 - filter("TABLE_NAME" LIKE 'HR%')       在官方文档上对这一特性的说明中也只有提到该参数才起作用： Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. --- The End ---