Oracle 11g之不可见的索引
以下内容摘自OTN
------------------------
您常常感到疑惑，索引是否真的有利于用户的查询？它可能有利于一个查询，但会影响 10 个其他查询。索引肯定会对 INSERT 语句造成负面影响，也会执行潜在的删除和更新操作，这取决于WHERE 条件是否在索引中包括该列。
一个相关的问题是，使用索引时，如果该索引被删除，会对查询性能造成什么影响？当然，您可以删除索引并查看对查询的影响，但说起来容易做起来难。索引实际上如何有助于查询？您必须重新定义索引，为此，需要进行重新创建。完全重新创建之后，就没有人能使用它了。重新创建索引也是一个昂贵的过程；它会占用许多有用的数据库资源。
您有办法创建一种对特定查询不可用同时又不会影响其他查询的索引吗？在 Oracle Database 11g 之前，不推荐使用 ALTER INDEX ...UNUSABLE，因为它会使表上的所有 DML 失败。但现在，您可以通过不可见的索引 精确使用该选项。简言之，您可以使索引对优化器“不可见”，这样就没有查询会使用它了。如果查询希望使用索引，则必须将其显式指定为提示。
下面是一个例子。假设有一个名为 RES 的表，并且您创建了如下所示的索引：
SQL> create index in_res_guest on res (guest_id);
SQL> select * from res where guest_id = 101;
Execution Plan----------------------Plan hash value: 1519600902 --------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| RES | 1 | 28 | 3 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IN_RES_GUEST | 1 | | 1 (0)| 00:00:01 |-------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("GUEST_ID"=101)SQL> alter index in_res_guest invisible; Index altered.
SQL> select * from res where guest_id = 101 2 / Execution Plan----------------------Plan hash value: 3824022422 --------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 1 | 28 | 140 (2)| 00:00:02 ||* 1 | TABLE ACCESS FULL| RES | 1 | 28 | 140 (2)| 00:00:02 |-------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("GUEST_ID"=101)SQL> select /*+ INDEX (res IN_RES_GUEST) */ res_id from res where guest_id = 101;--------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| RES | 1 | 28 | 3 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IN_RES_GUEST | 1 | | 1 (0)| 00:00:01 |--------------------------------------------------------
或者，您也可以设置会话级参数以使用不可见的索引：
SQL> alter session set optimizer_use_invisible_indexes = true;
SQL> select visibility 2 from user_indexes 3 where index_name = 'IN_RES_GUEST';VISIBILITY---------INVISIBLE
注意，如果您重新构建该索引，则该索引将变为可见。您必须再次将其显式设为不可见。
那么，该索引到底对“什么”不可见？当然，它不会对用户不可见。它只是对优化器不可见。常规数据库操作（如插入、更新和删除）将继续更新索引。创建不可见索引时应注意这一点；由于该索引，您将不会再次查看性能，但同时您在 DML 操作期间可能会付出一些代价。
-------------------------------------------------
以下是我自己做的例子，仅供参考：
------------------------------------------------
SCOTT@chan> create table test (id number, name varchar2(20));表已创建。SCOTT@chan> insert into test values (1, 'JACK');已创建 1 行。SCOTT@chan> insert into test values (2, 'DYLAN');已创建 1 行。SCOTT@chan> commit;提交完成。SCOTT@chan> create index test_idx on test(name);索引已创建。SCOTT@chan> set autotrace on explainSCOTT@chan> select * from test where name = 'DYLAN'; ID NAME---------- -------------------- 2 DYLAN执行计划----------------------Plan hash value: 1389866015----------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | TEST_IDX | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("NAME"='DYLAN')Note----- - dynamic sampling used for this statement (level=2)SCOTT@chan> alter index test_idx invisible;索引已更改。SCOTT@chan> select * from test where name = 'DYLAN'; ID NAME---------- -------------------- 2 DYLAN执行计划----------------------Plan hash value: 1357081020--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST | 1 | 25 | 3 (0)| 00:00:01 |--------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("NAME"='DYLAN')Note----- - dynamic sampling used for this statement (level=2)
SCOTT@chan> alter session set optimizer_use_invisible_indexes = true;会话已更改。
SCOTT@chan> select * from test where name = 'DYLAN'; ID NAME---------- -------------------- 2 DYLAN执行计划----------------------Plan hash value: 1389866015----------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | TEST_IDX | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("NAME"='DYLAN')Note----- - dynamic sampling used for this statement (level=2)SCOTT@chan> select visibility 2 from user_indexes 3 where index_name = 'TEST_IDX';VISIBILIT---------INVISIBLE
--------------------------
Present By Dylan.