从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博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-752007/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
