Oracle 索引的可見與隱藏（visible/invisible）

Oracle 索引的可見與隱藏（visible/invisible）

官方文檔：Making an Index Invisible

An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it. You cannot make an individual index partition invisible. Attempting to do so produces an error.

解釋：一個不可見的索引在最佳化器中被忽視，除非你主動地在會話或系統層級中設定OPTIMIZER_USE_INVISIBLE_INDEXES初始化參數為TRUE。標記不可見索引可以替代不可用索引或刪除索引。你不能讓分區索引不可見。試圖這麼做會產生一個錯誤。

測試：

1. 建立測試表ti，根據dba_objects表。

scott@ORCL>create table ti as select * from dba_objects;

Table created.

scott@ORCL>select count(*) from ti;

  COUNT(*)

----------

    72799

2. 根據object_id列建立索引ind_ti

scott@ORCL>create index ind_ti on ti(object_id);

Index created.

3. 調整為查看執行計畫

scott@ORCL>set autot trace exp
 

4. 測試索引是否生效被使用，結果索引被正常應用

scott@ORCL>select * from ti where object_id=20;

Execution Plan

----------------------------------------------------------

Plan hash value: 1655810896

--------------------------------------------------------------------------------------

| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time    |

--------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |        |    1 |  207 |    2  (0)| 00:00:01 |

|  1 |  TABLE ACCESS BY INDEX ROWID| TI    |    1 |  207 |    2  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IND_TI |    1 |      |    1  (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  2 - access("OBJECT_ID"=20)

Note

-----

  - dynamic sampling used for this statement (level=2)

 

5. 將ind_ti索引改為不可見

scott@ORCL>alter index ind_ti invisible;
 

6. 再次測試時，索引沒有被使用

scott@ORCL>select * from ti where object_id=20;

Execution Plan

----------------------------------------------------------

Plan hash value: 798420002

--------------------------------------------------------------------------

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |

--------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |      |    12 |  2484 |  291  (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| TI  |    12 |  2484 |  291  (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  1 - filter("OBJECT_ID"=20)

Note

-----

  - dynamic sampling used for this statement (level=2)

7. 資料修改

scott@ORCL>insert into ti select * from ti;

72799 rows created.

scott@ORCL>insert into ti select * from ti;

145598 rows created.

scott@ORCL>select count(*) from ti;

  COUNT(*)

----------

    291196

8. 將索引改為可見狀態

scott@ORCL>alter index ind_ti visible;

Index altered.

9. 測試索引是否生效被使用，結果索引被正常應用

scott@ORCL>set autot trace exp

scott@ORCL>select * from ti where object_id=20;

Execution Plan

----------------------------------------------------------

Plan hash value: 1655810896

--------------------------------------------------------------------------------------

| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time    |

--------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |        |    4 |  828 |    2  (0)| 00:00:01 |

|  1 |  TABLE ACCESS BY INDEX ROWID| TI    |    4 |  828 |    2  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IND_TI |    4 |      |    1  (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  2 - access("OBJECT_ID"=20)

Note

-----

  - dynamic sampling used for this statement (level=2)

總結：早期版本沒有該特性，如果想去掉索引進行測試，之後又想恢複索引，只能先將索引置為不能用或刪除。過後在rebuild index，需要花費不少時間，而且有可能會影響到業務工作效率。學習了該特性對以後最佳化SQL測試有很大協助。