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