11g新特性：Invisible Index

以往在最佳化的時候如果要禁用一個索引做測試時只能drop,而後重建(funcation based index除外)。

從11g索引可以有invisible和visible了，避免了重新建立索引的麻煩，並且當索引為invisible是對table的dml同樣會更新index

當索引被設為不可見後，實際上就是指該索引對於最佳化器不可見，而索引的正常更新並不受影響——即表在增、刪、改時，索引也會被更新。只是當最佳化器在選取查詢計劃時會“無視”該索引（無論是CBO或RBO）：

SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

 

SQL> select *From v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

 

SQL> conn scott/tiger
已串連。
SQL> create table t1 as select *From emp;

表已建立。

SQL> create index ii on t1(empno);

索引已建立。

SQL> set autot trace
SQL> select ename from t1 where empno=7788;

 

執行計畫
----------------------------------------------------------
Plan hash value: 2627886109

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

SQL> alter index ii invisible;

索引已更改。

SQL> SELECT INDEX_NAME, VISIBILITY
  2  FROM USER_INDEXES
  3  WHERE INDEX_NAME ='II';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
II                             INVISIBLE

SQL> select ename from t1 where empno=7788;

未選定行

執行計畫
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    20 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    20 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

 

 

 

SQL> alter index ii visible;

索引已更改。

 

 

 

SQL> SELECT INDEX_NAME, VISIBILITY
  2  FROM USER_INDEXES
  3  WHERE INDEX_NAME ='II';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
II                             VISIBLE

 

 

 

 

SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
optimizer_use_invisible_indexes      boolean     FALSE

SQL> alter  system set  optimizer_use_invisible_indexes=true;

系統已更改。

SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
optimizer_use_invisible_indexes      boolean     TRUE

SQL> alter index ii invisible ;

SQL> select ename from t1 where empno=7788;

未選定行

執行計畫
----------------------------------------------------------
Plan hash value: 2627886109

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