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