基於索引的SQL語句最佳化之降龍十八掌(2)[@more@]

增加查詢的範圍限制

增加查詢的範圍限制，避免全範圍的搜尋。

例3：以下查詢表record 中時間ActionTime小於2001年3月1日的資料：

select * from record where ActionTime < to_date ('20010301' ,'yyyymm')

查詢計劃表明，上面的查詢對錶進行全表掃描，如果我們知道表中的最早的資料為2001年1月1日，那麼，可以增加一個最小時間，使查詢在一個完整的範圍之內。修改如下： select * from record where

ActionTime < to_date ('20010301' ,'yyyymm')

and ActionTime > to_date ('20010101' ,'yyyymm')

後一種SQL語句將利用上ActionTime欄位上的索引，從而提高查詢效率。把'20010301'換成一個變數，根據取值的機率，可以有一半以上的機會提高效率。同理，對於大於某個值的查詢，如果知道當前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。

儘量去掉"IN"、"OR"

含有"IN"、"OR"的Where子句常會使用工作表，使索引失效；如果不產生大量重複值，可以考慮把子句拆開；拆開的子句中應該包含索引。

例4： select count(*) from stuff where id_no in('0','1')（23秒）

可以考慮將or子句分開：

select count(*) from stuff where id_no='0'

select count(*) from stuff where id_no='1'

然後再做一個簡單的加法，與原來的SQL語句相比，查詢速度更快。

儘量去掉 "<>"

儘量去掉 "<>"，避免全表掃描，如果資料是列舉值，且取值範圍固定，則修改為"OR"方式。

例5：

UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;

以上語句由於其中包含了"<>"，執行計劃中用了全表掃描（TABLE ACCESS FULL），沒有用到state欄位上的索引。實際應用中，由於業務邏輯的限制，欄位state為列舉值，只能等於0，1或2，而且，值等於=1，2的很少，因此可以去掉"<>"，利用索引來提高效率。

修改為：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。進一步的修改可以參考第4種方法。

去掉Where子句中的IS NULL和IS NOT NULL

Where字句中的IS NULL和IS NOT NULL將不會使用索引而是進行全表搜尋，因此需要透過改變查詢方式，分情況討論等方法，去掉Where子句中的IS NULL和IS NOT NULL。

索引提高資料分佈不均勻時查詢效率

索引的選擇性低，但資料的值分佈差異很大時，仍然可以利用索引提高效率。A、資料分佈不均勻的特殊情況下，選擇性不高的索引也要建立。

表ServiceInfo中資料量很大，假設有一百萬行，其中有一個欄位DisposalCourseFlag，取值範圍為列舉值：[0，1，2，3，4，5，6，7]。按照前面說的索引建立的規則，“選擇性不高的欄位不應該建立索引，該欄位只有8種取值，索引值的重複率很高，索引選擇性明顯很低，因此不建索引。然而，由於該欄位上資料值的分佈情況非常特殊，具體如下表：

取值範圍	1~5	6	7
佔總資料量的百分比	1%	98%	1%

而且，常用的查詢中，查詢DisposalCourseFlag<6 的情況既多又頻繁，毫無疑問，如果能夠建立索引，並且被應用，那麼將大大提高這種情況的查詢效率。因此，我們需要在該欄位上建立索引。

利用HINT強制指定索引

在ORACLE最佳化器無法用上合理索引的情況下，利用HINT強制指定索引。

繼續上面7的例子，ORACLE預設認定，表中列的值是在所有資料行中均勻分佈的，也就是說，在一百萬資料量下，每種DisposalCourseFlag值各有12.5萬資料行與之對應。假設SQL搜尋條件DisposalCourseFlag=2，利用DisposalCourseFlag列上的索引進行資料搜尋效率，往往不比全表掃描的高，ORACLE因此對索引“視而不見”，從而在查詢路徑的選擇中，用其他欄位上的索引甚至全表掃描。根據我們上面的分析，資料值的分佈很特殊，嚴重的不均勻。為了利用索引提高效率，此時，一方面可以單獨對該欄位或該表用analyze語句進行分析，對該列蒐集足夠的統計資料，使ORACLE在查詢選擇性較高的值時能用上索引；另一方面，可以利用HINT提示，在SELECT關鍵字後面，加上“/*+ INDEX(表名稱，索引名稱）*/”的方式，強制ORACLE最佳化器用上該索引。

比如： select * from serviceinfo where DisposalCourseFlag=1 ;

上面的語句，實際執行中ORACLE用了全表掃描，加上藍色提示部分後，用到索引查詢。如下：

select /*+ INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG) */ *

from serviceinfo where DisposalCourseFlag=1;

請注意，這種方法會加大程式碼維護的難度，而且該欄位上索引的名稱被改變之後，必須要同步所有指定索引的HINT程式碼，否則HINT提示將被ORACLE忽略掉。

基於索引的SQL語句最佳化之降龍十八掌(2)(轉)