11g新特性--invisible index

lsq_008發表於2013-01-02
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章