索引的管理
?
??? 在昨天學習完了索引的基本用法之后，今天繼續學習一下對索引的管理。基本上就是一些日常的操作語法，以及數據字典的掌握。記下來也可以在今后用來查詢。另外，本文的最后幾章，關于索引的用法，在平常的使用中是非常有用的，一定要仔細掌握！！！下面開始：
?
?
合并與重建索引：
?
??? 隨著對表的不斷更新，表中的索引會產生越來越多的存儲碎片，要對碎片進行整理有兩種方式：重建索引或合并索引。
?
??? 合并索引：將B樹索引中的葉節點存儲碎片進行合并。
??? ALTER INDEX ind_t COALESCE deallocate unused;
??? 注：不加deallocate unused則合并后仍保留多于的空間。
?
??? 重建索引：即進行重新建立，而且可以修改之前的各種參數。
??? ALTER INDEX ind_t REBUILD
??? tablespace users
??? storage(initial 128k
??????????? next 64k);
?
??? 注：在改變屬性時還可以修改REVERSE參數，改為反向，或反向改為正向。
?
?
??? 對于分區表，一次只能重建其中的一個分區。
??? ALTER INDEX ind_t rebuild
??? partition p1;
?
?
監視索引：
?
??? 監視索引的使用情況：
???? 打開：ALTERINDEX INDEX_T5 MONITORINGUSAGE;
???? 查看：select * from v$object_usage;
???? 關閉：ALTERINDEX INDEX_T5 NOMONITORINGUSAGE;
?
???
監視索引空間使用：
???? 分析：ANALYZEINDEX INDEX_T5 VALIDATESTRUCTURE;
???? 查看：select br_rows,br_blks,lf_rows,del_lf_rows from index_stats;
???? 說明：BR_ROWS為B樹分支數，LF_ROWS為B樹葉節點數，DEL_LF_ROWS刪除但保存在結構中的節點數。
???? 注：當30%的葉節點是已經刪除的葉節點時，則應該考慮重建和合并索引。
?
?
刪除索引：
?
??? 當一下情況時需要刪除索引：
?
??? 1、不再需要的索引；
??? 2、通過一段時間觀察，發現很少使用該索引；
??? 3、該索引無效，必須在重建之前刪除；
??? 4、該索引包含過多存儲碎片，需要在重建之前刪除該索引；
??? 5、索引沒有提供所期望的性能改善；
雖然索引的建立沒有限制，但是也不是說建立的越多越好，建立所以需要一下的代價：
?
??? 1、基礎表維護時，系統要同時維護索引；
??? 2、插入、更新、刪除數據時需要同時修改索引；
??? 3、在更改數據時會產生大量db file sequential read鎖等待；
?
??? DROP INDEX ind_t;
?
?
索引使用的限制情況：
?
??? 限制索引是一些沒有經驗的開發人員經常犯的錯誤之一。在SQL中有很多陷阱會使一些索引無法使用。具體有如下情況：
?
??? 1、
使用不等于操作符（<>、!=）
??????? 下面的查詢即使在cust_rating列有一個索引，查詢語句仍然執行一次全表掃描。
??????? select cust_Id,cust_name
??????? from?? customers
??????? where? cust_rating <> 'aa';
??????? 把上面的語句改成如下的查詢語句，在采用基于規則的優化器而不是基于代價的優化器時，將會使用索引。
??????? select cust_Id,cust_name
??????? from?? customers
??????? where? cust_rating < 'aa' or cust_rating > 'aa';
???????
注：通過把不等于操作符改成OR條件，就可以使用索引，以避免全表掃描。
????
??? 2、使用IS NULL 或IS NOT NULL
??????? 使用IS NULL 或IS NOT NULL同樣會限制索引的使用。因為NULL值并沒有被定義。
??????? 在SQL語句中使用NULL會有很多的麻煩。因此建議開發人員在建表時，把需要索引的列設成NOT NULL。
??????? 如果被索引的列在某些行中存在NULL值，就不會使用這個索引，除非索引是一個位圖索引。
?
???
3、使用函數
??????? 如果不使用基于函數的索引，那么在SQL語句的WHERE子句中對存在索引的列使用函數時，會使優化器忽略掉這些索引。
??????? select empno,ename,deptno
??????? from?? emp
??????? where? trunc(hiredate)='01-MAY-81';
??????? 把上面的語句改成下面的語句，這樣就可以通過索引進行查找。
??????? select empno,ename,deptno
??????? from?? emp
??????? where? hiredate<(to_date('01-MAY-81')+0.9999);
?
??????? 使用這個方法也可以屏蔽不想使用的不良索引，例如：
??????? 數值型：在索引字段上加0，例如
??????? select * from t1 where empno+0 = v_empno;
??????? 字符型：在索引字段上加''，例如
??????? select * from t1 where type||'' = v_type;
?
???
4、比較不匹配的數據類型
??????? 比較不匹配的數據類型也是比較難于發現的性能問題之一。
??????? 注意下面查詢的例子，account_number是一個VARCHAR2類型，在account_number字段上有索引。下面的語句將執行全表掃描。
??????? select bank_name,address,city,state,zip
??????? from?? banks
??????? where? account_number = 990354;
??????? Oracle可以自動把where子句變成to_number(account_number)=990354，這樣就限制了索引的使用,改成下面的查詢就可以使用索引：
??????? select bank_name,address,city,state,zip
??????? from?? banks
??????? where? account_number ='990354';
???????
注：不匹配的數據類型之間比較會讓Oracle自動限制索引的使用，即便對這個查詢執行Explain Plan也不能讓您明白為什么做了一次“全表掃描”。
?
??? 5、當使系統數據字典或視圖
??????? 系統的數據字典表都未被分析過，可能導致極差的“執行計劃”。但是不要擅自對數據字典表做分析，否則可能導致死鎖，或系統性能下降。
?
?
創建索引的規則：
?
??? 1、經常檢索排序大表中40%或非排序表7%的行，建議建索引；
??? 2、為了改善多表關聯，索引列用于聯結；
??? 3、列中的值相對比較唯一；
??? 4、
取值范圍（大：B樹索引，小：位圖索引）；
??? 5、Date型列一般適合基于函數的索引；
??? 6、列中有許多空值，不適合建立索引；
?
??? 7、建議每張表不超過5個索引；
?
??? 8、經常一起使用多個字段檢索記錄，組合索引比單索引更有效，把最常用的列放在最前面；
?
??? 9、合理設定pctfress，注意：不能給索引指定pctused；
?
?
?
?