索引之显、隐

索引维护是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

本文章 转自：

http://tech.it168.com/a2009/0915/679/000000679666.shtml