11G的不可见索引

不可见索引是11G的新特性，不可见并非物理上看不见，其实还是存在的。ORACLE
还会进行维护，只是ORACLE并不会使用它。 这在维护索引的时候非常有用，譬如，可以把
这个索引标志为不可见，看看系统的性能如何。如果没人使用到索引，可以DROP掉了，如果有人要用到这个索引，可以在标记为可见。省去了假设性能不好在重建对系统的影响。

SQL> conn TEST/test
Connected.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(30)

SQL> create index idx_t_01 on t(id) invisible;

Index created.

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

PL/SQL procedure successfully completed.

SQL> select index_name,visibility from user_indexes;

INDEX_NAME                     VISIBILIT
------------------------------ ---------
IDX_T_01                       INVISIBLE

SQL> set autot traceonly exp
SQL> set linesize 200
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 |    22 |    13   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    22 |    13   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1)

SQL> set autot off
SQL> alter index idx_t_01 visible;

Index altered.

SQL> select index_name,visibility from user_indexes;

INDEX_NAME                     VISIBILIT
------------------------------ ---------
IDX_T_01                       VISIBLE

SQL> set autot traceonly exp
SQL> select * from t where id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 488553849

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_01 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

SQL>