11g新特性--invisible index

从11g开始，可以创建一个invisible的索引，优化器会忽略这个索引，但DML操作会维护索引，这可以在删除索引之前，用来测试对查询是否有影响。

SQL> create table t as select rownum id from dual connect by level<=100000;

Table created.

SQL> create index idx_t on t(id) tablespace users;

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t');

PL/SQL procedure successfully completed.

SQL> set autot traceonly explain

SQL> select * from t where id=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 2296882198

--------------------------------------------------------------------------

| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT |       |     1 |     5 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IDX_T |     1 |     5 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("ID"=1)

SQL> drop index idx_t;

Index dropped.

SQL> create index idx_t on t(id) tablespace users invisible;

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t');

PL/SQL procedure successfully completed.

SQL> select * from t where id=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     5 |    47   (3)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |     5 |    47   (3)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ID"=1)

可见，当索引为invisible时，优化器会忽略该索引，将索引状态改为visible后，优化器又可以使用索引了

SQL> alter index idx_t visible;

Index altered.

SQL> select * from t where id=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 2296882198

--------------------------------------------------------------------------

| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT |       |     1 |     5 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IDX_T |     1 |     5 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("ID"=1)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-752007/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-752007/