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

遮蔽無用索引

繼續上面8的例子，由於實際查詢中，還有涉及到DisposalCourseFlag=6的查詢，而此時如果用上該欄位上的索引，將是非常不明智的，效率也極低。因此這種情況下，我們需要用特殊的方法遮蔽該索引，以便ORACLE選擇其他欄位上的索引。比如，如果欄位為數值型的就在表示式的欄位名後，新增“+ 0”，為字元型的就並上空串：“||""”

如： select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = '36' 。

不過，不要把該用的索引遮蔽掉了，否則同樣會產生低效率的全表掃描。

分解複雜查詢，用常量代替變數

對於複雜的Where條件組合，Where中含有多個帶索引的欄位，考慮用IF語句分情況進行討論；同時，去掉不必要的外來引數條件，減低複雜度，以便在不同情況下用不同欄位上的索引。

繼續上面9的例子，對於包含

Where (DisposalCourseFlag < v_DisPosalCourseFlag) or (v_DisPosalCourseFlag is null) and ....的查詢，(這裡v_DisPosalCourseFlag為一個輸入變數，取值範圍可能為[NULL，0，1，2，3，4，5，6，7])，可以考慮分情況用IF語句進行討論，類似：

IF v_DisPosalCourseFlag =1 THEN

Where DisposalCourseFlag = 1 and ....

ELSIF v_DisPosalCourseFlag =2 THEN

Where DisposalCourseFlag = 2 and ....

。。。。。。

子句儘量前端匹配

因為like引數使用的非常頻繁，因此如果能夠對like子句使用索引，將很高的提高查詢的效率。

例6：select * from city where name like ‘%S%’

以上查詢的執行計劃用了全表掃描（TABLE ACCESS FULL），如果能夠修改為：

select * from city where name like ‘S%’

那麼查詢的執行計劃將會變成（INDEX RANGE SCAN），成功的利用了name欄位的索引。這意味著Oracle SQL最佳化器會識別出用於索引的like子句，只要該查詢的匹配端是具體值。因此我們在做like查詢時，應該儘量使查詢的匹配端是具體值，即使用like ‘S%’。

用Case語句合併多重掃描

我們常常必須基於多組資料表計算不同的聚集。例如下例透過三個獨立查詢：

例8：1）select count(*) from emp where sal<1000;

2）select count(*) from emp where sal between 1000 and 5000;

3）select count(*) from emp where sal>5000;

這樣我們需要進行三次全表查詢，但是如果我們使用case語句：

select

count (sale when sal <1000

then 1 else null end) count_poor,

count (sale when between 1000 and 5000

then 1 else null end) count_blue_collar,

count (sale when sal >5000

then 1 else null end) count_poor

from emp;

這樣查詢的結果一樣，但是執行計劃只進行了一次全表查詢。

使用nls_date_format

例9：

select * from record where to_char(ActionTime,'mm')='12'

這個查詢的執行計劃將是全表查詢，如果我們改變nls_date_format，

SQL>alert session set nls_date_formate=’MM’;

現在重新修改上面的查詢：

select * from record where ActionTime='12'

這樣就能使用actiontime上的索引了，它的執行計劃將是（INDEX RANGE SCAN）。

使用基於函式的索引

前面談到任何對列的操作都可能導致全表掃描，例如：

select * from emp where substr(ename,1,2)=’SM’;

但是這種查詢在客服系統又經常使用，我們可以建立一個帶有substr函式的基於函式的索引，

create index emp_ename_substr on eemp ( substr(ename,1,2) );

這樣在執行上面的查詢語句時，這個基於函式的索引將排上用場，執行計劃將是（INDEX RANGE SCAN）。

基於函式的索引要求等式匹配

上面的例子中，我們建立了基於函式的索引，但是如果執行下面的查詢：

select * from emp where substr(ename,1,1)=’S’

得到的執行計劃將還是（TABLE ACCESS FULL），因為只有當資料列能夠等式匹配時，基於函式的索引才能生效，這樣對於這種索引的計劃和維護的要求都很高。請注意，向表中新增索引是非常危險的操作，因為這將導致許多查詢執行計劃的變更。然而，如果我們使用基於函式的索引就不會產生這樣的問題，因為Oracle只有在查詢使用了匹配的內建函式時才會使用這種型別的索引。

使用分割槽索引

在用分析命令對分割槽索引進行分析時，每一個分割槽的資料值的範圍資訊會放入Oracle的資料字典中。Oracle可以利用這個資訊來提取出那些只與SQL查詢相關的資料分割槽。

例如，假設你已經定義了一個分割槽索引，並且某個SQL語句需要在一個索引分割槽中進行一次索引掃描。Oracle會僅僅訪問這個索引分割槽，而且會在這個分割槽上呼叫一個此索引範圍的快速全掃描。因為不需要訪問整個索引，所以提高了查詢的速度。

使用點陣圖索引

點陣圖索引可以從本質上提高使用了小於1000個唯一資料值的資料列的查詢速度，因為在點陣圖索引中進行的檢索是在RAM中完成的，而且也總是比傳統的B樹索引的速度要快。對於那些少於1000個唯一資料值的資料列建立點陣圖索引，可以使執行效率更快。

決定使用全表掃描還是使用索引

和所有的秘笈一樣，最後一招都會又回到起點，最後我們來討論一下是否需要建立索引，也許進行全表掃描更快。在大多數情況下，全表掃描可能會導致更多的物理磁碟輸入輸出，但是全表掃描有時又可能會因為高度並行化的存在而執行的更快。如果查詢的表完全沒有順序，那麼一個要返回記錄數小於10％的查詢可能會讀取表中大部分的資料塊，這樣使用索引會使查詢效率提高很多。但是如果表非常有順序，那麼如果查詢的記錄數大於40％時，可能使用全表掃描更快。因此，有一個索引範圍掃描的總體原則是：

1）對於原始排序的表 僅讀取少於表記錄數40％的查詢應該使用索引範圍掃描。反之，讀取記錄數目多於表記錄數的40％的查詢應該使用全表掃描。

2）對於未排序的表 僅讀取少於表記錄數7％的查詢應該使用索引範圍掃描。反之，讀取記錄數目多於表記錄數的7％的查詢應該使用全表掃描。

以上的招式，是完全可以相互結合同時運用的。而且各種方法之間相互影響，緊密聯絡。這種聯絡既存在一致性，也可能帶來衝突，當衝突發生時，需要根據實際情況進行選擇，沒有固定的模式。最後決定SQL最佳化功力的因素就是對ORACLE內功的掌握程度了。

另外，值得注意的是：隨著時間的推移和資料的累計與變化，ORACLE對SQL語句的執行計劃也會改變，比如：基於代價的最佳化方法，隨著資料量的增大，最佳化器可能錯誤的不選擇索引而採用全表掃描。這種情況可能是因為統計資訊已經過時，在資料量變化很大後沒有及時分析表；但如果對錶進行分析之後，仍然沒有用上合理的索引，那麼就有必要對SQL語句用HINT提示，強制用合理的索引。但這種HINT提示也不能濫用，因為這種方法過於複雜，缺乏通用性和應變能力，同時也增加了維護上的代價；相對來說，基於函式右移、去掉“IN ，OR ，<> ，IS NOT NULL ”、分解複雜的SQL語句等等方法，卻是“放之四海皆準”的，可以放心大膽的使用。

同時，最佳化也不是“一勞永逸”的，必須隨著情況的改變進行相應的調整。當資料庫設計發生變化，包括更改表結構：欄位和索引的增加、刪除或改名等；業務邏輯發生變化：如查詢方式、取值範圍發生改變等等。在這種情況下，也必須對原有的最佳化進行調整，以適應效率上的需求。