oracle sql最佳化

標籤：

第一掌 避免對列的操作

任何對列的操作都可能導致全表掃描，這裡所謂的操作包括資料庫函數、計算運算式等等，查詢時要儘可能將操作移至等式的右邊，甚至去掉函數。  

例1：下列SQL條件陳述式中的列都建有恰當的索引，但30萬行資料情況下執行速度卻非常慢：  

select * from record where  substrb(CardNo,1,4)=‘5378‘(13秒) 

select * from record where  amount/30< 1000（11秒） 

select * from record where  to_char(ActionTime,‘yyyymmdd‘)=‘19991201‘（10秒） 

由於where子句中對列的任何操作結果都是在SQL運行時逐行計算得到的，因此它不得不進行表掃描，而沒有使用該列上面的索引；如果這些結果在查詢編譯時間就能得到，那麼就可以被SQL最佳化器最佳化，使用索引，避免表掃描，因此將SQL重寫如下：

select * from record where CardNo like  ‘5378%‘（< 1秒）

select * from record where amount  < 1000*30（< 1秒）

select * from record where ActionTime= to_date (‘19991201‘ ,‘yyyymmdd‘)（< 1秒）

差別是很明顯的！

第二掌 避免不必要的類型轉換

需要注意的是，盡量避免潛在的資料類型轉換。如將字元型資料與數值型資料比較，ORACLE會自動將字元型用to_number()函數進行轉換，從而導致全表掃描。

例2：表tab1中的列col1是字元型（char)，則以下語句存在類型轉換：

select col1,col2 from tab1 where col1>10，

應該寫為： select col1,col2 from tab1 where col1>‘10‘。

第三掌 增加查詢的範圍限制

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

例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忽略掉。

第九掌 屏蔽無用索引

繼續上面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參數使用的非常頻繁，因此如果能夠對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％的查詢應該使用全表掃描。

2         總結

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

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

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

oracle sql最佳化