|
不可见索引是G的新特性不可见并非物理上看不见其实还是存在的ORACLE 还会进行维护只是ORACLE并不会使用它 这在维护索引的时候非常有用譬如可以把 这个索引标志为不可见看看系统的性能如何如果没人使用到索引可以DROP掉了如果有人要用到这个索引可以在标记为可见省去了假设性能不好在重建对系统的影响 SQL> conn TEST/test Connected SQL> desc t Name Null? Type ID NUMBER() NAME VARCHAR() SQL> create index idx_t_ on t(id) invisible; Index created SQL> exec dbms_statsgather_table_stats(usertcascade=>true); PL/SQL procedure successfully completed SQL> select index_namevisibility from user_indexes; INDEX_NAME VISIBILIT IDX_T_ INVISIBLE SQL> set autot traceonly exp SQL> set linesize SQL> select * from t where id=; Execution Plan
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| :: | |* | TABLE ACCESS FULL| T | | | ()| :: |
Predicate Information (identified by operation id):
filter(ID=) SQL> set autot off SQL> alter index idx_t_ visible; Index altered SQL> select index_namevisibility from user_indexes; INDEX_NAME VISIBILIT IDX_T_ VISIBLE SQL> set autot traceonly exp SQL> select * from t where id=; Execution Plan
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| :: | | | TABLE ACCESS BY INDEX ROWID| T | | | ()| :: | |* | INDEX RANGE SCAN | IDX_T_ | | | ()| :: |
Predicate Information (identified by operation id):
access(ID=) SQL> |
