Oracle隱藏索引和索引可用性

在我參與過的許多商店系統開發中，我發現在生產系統中創建一個索引並不需要經過詳細的論證，甚至連應用程序代碼也沒有檢查就創建了，大多數時候都是靠個人經驗和感覺行事的，人們往往只會思考與創建索引有關的列是否會受到影響，完全靠臨場反應，到最後數據庫中往往有上百個索引創建了但可能從未使用過，或對SQL執行性能有負面影響。作為一名DBA，我們有責任找到並清除這些閒置的以及對性能有負面影響的索引。但我們從哪裡開始呢?其實Oracle已經為我們提供了解決之道。

　　有兩種基本的情況：

　　1、 我們必須確定索引是否被使用，如果索引沒有使用，只需要刪除它就可以了。

　　2、 如果索引被使用了，或認為索引將會被使用，對於這種索引，要確定索引對數據庫性能的影響稍微有點難度。

　　對於第一種情況(判斷索引是否被使用)，我們可以對數據庫索引進行監視，關鍵是要監視足夠長的時間，可以監視一小時，一天，一周或一個業務季度，這要取決於表上的索引是與什麼相關的。

　　那該如何監視一個索引呢?其實簡單得很，只需要使用ALTER INDEX命令，加上MONITORING USAGE子句就可以了，還是來看一看實例吧：

SQL> ALTER INDEX pk_emp MONITORING USAGE;

　　Index altered.

　　SQL> ALTER INDEX ix_emp_sal MONITORING USAGE;

　　Index altered.

　　當你在該表上進行SELECT，UPDATE，DELETE(沒有INSERT)時，一旦使用了索引，就會在V$OBJECT_USAGE動態視圖中將該索引標記為在使用中：

　SQL> select * from emp where empno = 7844;

　　EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

　　7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

　　SQL> SELECT v.index_name, v.table_name,

　　v.monitoring, v.used,

　　start_monitoring, end_monitoring

　　FROM v$object_usage v, user_indexes u

　　WHERE v.index_name = u.index_name;

　　INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

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

　　PK_EMP EMP YES YES 04/28/2009 10:16:51

　　IX_EMP_SAL EMP YES NO 04/28/2009 10:17:01

　　就這麼簡單。顯然，如果前面的SELECT語句只是查詢EMP表，或許我們應該刪除掉IX_EMP_SAL索引。

　　如果索引已經被使用，或將被使用，在采取行動(如刪除或創建索引)之前，我們必須放聰明點。為了幫助解決這些問題，Oracle為我們提供了一個新特性，INVISIBLE索引，允許我們將索引隱藏起來，隱藏的索引不能使用，但仍然可以通過INSERT，UPDATE和DELETE進行維護。要使一個索引不可見，可以使用CREATE或ALTER INDEX INVISIBLE命令，這裡以上面的IX_EMP_SAL索引為例進行演示：

SQL> create index ix_emp_sal on emp(sal) INVISIBLE;

　　SQL> alter index ix_emp_sal INVISIBLE;

　　當一個索引被置為INVISIBLE時，應用程序就看不到它了，也不能在任何DML操作中使用它了。優化器也看不到隱藏索引，因此也不會被任何執行計劃使用，除非明確指定了一個提示(hint)，會話被設置為使用隱藏索引，或者數據庫被設置為可以使用所有的隱藏索引，這正是某些DBA夢寐以求的功能，使用一個新的init.ora參數optimizer_use_invisible_indexes，你可以固定會話，或全系統范圍內都可以使用隱藏索引，讓你有機會測試新建索引在完全移動到生產環境之前的影響，可以通過設置這個初始化參數使用隱藏索引，或在SQL中增加提示使用隱藏索引，如：　

SQL> alter system set optimizer_use_invisible_indexetrue;

　　SQL> alter session set optimizer_use_invisible_indexetrue;

　　SQL> select * /*+ index (emp ix_ep_sal) */ ename from emp where sal=1500;

　　要使一個索引從不可見狀態變為可見狀態，使用ALTER INDEX語句+ VISIBLE關鍵字即可：

SQL> alter index ix_emp_sal VISIBLE;

　　此外，如果你想找出在你的數據庫中哪些索引是隱藏的，可以查詢DBA_，USER_或ALL_INDEXES視圖中的VISIBILITY列。

　SQL>select index_name, visibility

　　from dba_indexes

　　where index_name='IX_EMP_SAL';

　　INDEX_NAME VISIBILIT

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

　　IX_EMP_SAL INVISIBLE

　　在運用INVISIBLE索引功能時要注意由其它方法創建或修改的隱藏索引，如果你以前寫有一個SQL查看索引的結構，現在需要修改一下增加檢查VISIBILITY列，否則你看到的僅僅是索引的部分信息，當執行了大量的INSERT，UPDATE或DELETE操作時，這可能會變成一個隱藏的惡夢。

　　索引的可用性一向有些黑色藝術，對於刪除一個索引是否會對性能產生影響從來都沒有明確的判斷標准，現在通過監視索引的可用性，並可以修改索引的可見性，DBA完全可以更好地測試和驗證索引的可用性了。