第一掌 避免對(duì)列的操作

任何對(duì)列的操作都可能導(dǎo)致全表掃描,，這里所謂的操作包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等,，查詢時(shí)要盡可能將操作移至等式的右邊,，甚至去掉函數(shù)。  

例1：下列SQL條件語句中的列都建有恰當(dāng)?shù)乃饕?，?0萬行數(shù)據(jù)情況下執(zhí)行速度卻非常慢：  

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子句中對(duì)列的任何操作結(jié)果都是在SQL運(yùn)行時(shí)逐行計(jì)算得到的,，因此它不得不進(jìn)行表掃描，而沒有使用該列上面的索引,；如果這些結(jié)果在查詢編譯時(shí)就能得到,，那么就可以被SQL優(yōu)化器優(yōu)化，使用索引,，避免表掃描,，因此將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秒）

差別是很明顯的！

第二掌 避免不必要的類型轉(zhuǎn)換

需要注意的是,，盡量避免潛在的數(shù)據(jù)類型轉(zhuǎn)換,。如將字符型數(shù)據(jù)與數(shù)值型數(shù)據(jù)比較，ORACLE會(huì)自動(dòng)將字符型用to_number()函數(shù)進(jìn)行轉(zhuǎn)換,，從而導(dǎo)致全表掃描,。

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

select col1,col2 from tab1 where col1>10,，

應(yīng)該寫為： select col1,col2 from tab1 where col1>'10',。

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

增加查詢的范圍限制，避免全范圍的搜索,。

例3：以下查詢表record 中時(shí)間ActionTime小于2001年3月1日的數(shù)據(jù)：

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

查詢計(jì)劃表明,，上面的查詢對(duì)表進(jìn)行全表掃描，如果我們知道表中的最早的數(shù)據(jù)為2001年1月1日,，那么,，可以增加一個(gè)最小時(shí)間,，使查詢?cè)谝粋€(gè)完整的范圍之內(nèi)。修改如下： select * from record where

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

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

后一種SQL語句將利用上ActionTime字段上的索引,，從而提高查詢效率,。把'20010301'換成一個(gè)變量，根據(jù)取值的機(jī)率,，可以有一半以上的機(jī)會(huì)提高效率,。同理，對(duì)于大于某個(gè)值的查詢,，如果知道當(dāng)前可能的最大值,，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。

第四掌 盡量去掉"IN",、"OR"

含有"IN",、"OR"的Where子句常會(huì)使用工作表，使索引失效,；如果不產(chǎn)生大量重復(fù)值,，可以考慮把子句拆開,；拆開的子句中應(yīng)該包含索引,。  

例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'

然后再做一個(gè)簡單的加法,，與原來的SQL語句相比，查詢速度更快,。

第五掌 盡量去掉 "<>"

盡量去掉 "<>",，避免全表掃描，如果數(shù)據(jù)是枚舉值,，且取值范圍固定,，則修改為"OR"方式。

例5：

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

以上語句由于其中包含了"<>",，執(zhí)行計(jì)劃中用了全表掃描（TABLE ACCESS FULL）,，沒有用到state字段上的索引。實(shí)際應(yīng)用中,，由于業(yè)務(wù)邏輯的限制,，字段state為枚舉值，只能等于0,，1或2,，而且,，值等于=1,，2的很少，因此可以去掉"<>",，利用索引來提高效率,。

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

第六掌 去掉Where子句中的IS NULL和IS NOT NULL

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

第七掌 索引提高數(shù)據(jù)分布不均勻時(shí)查詢效率

索引的選擇性低,，但數(shù)據(jù)的值分布差異很大時(shí)，仍然可以利用索引提高效率,。A,、數(shù)據(jù)分布不均勻的特殊情況下，選擇性不高的索引也要?jiǎng)?chuàng)建,。

表ServiceInfo中數(shù)據(jù)量很大,，假設(shè)有一百萬行，其中有一個(gè)字段DisposalCourseFlag,，取值范圍為枚舉值：[0,，1，2,，3,，4，5,，6,，7]。按照前面說的索引建立的規(guī)則,，“選擇性不高的字段不應(yīng)該建立索引,，該字段只有8種取值，索引值的重復(fù)率很高,，索引選擇性明顯很低,，因此不建索引。然而,，由于該字段上數(shù)據(jù)值的分布情況非常特殊,，具體如下表：

而且，常用的查詢中,，查詢DisposalCourseFlag<6 的情況既多又頻繁,，毫無疑問，如果能夠建立索引,，并且被應(yīng)用,，那么將大大提高這種情況的查詢效率。因此，我們需要在該字段上建立索引,。

第八掌 利用HINT強(qiáng)制指定索引

在ORACLE優(yōu)化器無法用上合理索引的情況下,，利用HINT強(qiáng)制指定索引。

繼續(xù)上面7的例子,，ORACLE缺省認(rèn)定,，表中列的值是在所有數(shù)據(jù)行中均勻分布的，也就是說,，在一百萬數(shù)據(jù)量下,，每種DisposalCourseFlag值各有12.5萬數(shù)據(jù)行與之對(duì)應(yīng)。假設(shè)SQL搜索條件DisposalCourseFlag=2,，利用DisposalCourseFlag列上的索引進(jìn)行數(shù)據(jù)搜索效率,，往往不比全表掃描的高，ORACLE因此對(duì)索引“視而不見”,，從而在查詢路徑的選擇中,，用其他字段上的索引甚至全表掃描。根據(jù)我們上面的分析,，數(shù)據(jù)值的分布很特殊,，嚴(yán)重的不均勻。為了利用索引提高效率,，此時(shí),，一方面可以單獨(dú)對(duì)該字段或該表用analyze語句進(jìn)行分析，對(duì)該列搜集足夠的統(tǒng)計(jì)數(shù)據(jù),，使ORACLE在查詢選擇性較高的值時(shí)能用上索引,；另一方面，可以利用HINT提示,，在SELECT關(guān)鍵字后面,，加上“/*+ INDEX(表名稱，索引名稱）*/”的方式,，強(qiáng)制ORACLE優(yōu)化器用上該索引,。

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

上面的語句，實(shí)際執(zhí)行中ORACLE用了全表掃描,，加上藍(lán)色提示部分后,，用到索引查詢。如下：

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

from  serviceinfo where DisposalCourseFlag=1;

請(qǐng)注意,，這種方法會(huì)加大代碼維護(hù)的難度,，而且該字段上索引的名稱被改變之后，必須要同步所有指定索引的HINT代碼,，否則HINT提示將被ORACLE忽略掉,。

第九掌 屏蔽無用索引

繼續(xù)上面8的例子，由于實(shí)際查詢中，還有涉及到DisposalCourseFlag=6的查詢,，而此時(shí)如果用上該字段上的索引，將是非常不明智的,，效率也極低,。因此這種情況下，我們需要用特殊的方法屏蔽該索引,，以便ORACLE選擇其他字段上的索引,。比如，如果字段為數(shù)值型的就在表達(dá)式的字段名后,，添加“+ 0”,，為字符型的就并上空串：“||""”

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

不過,，不要把該用的索引屏蔽掉了,，否則同樣會(huì)產(chǎn)生低效率的全表掃描。

第十掌 分解復(fù)雜查詢,，用常量代替變量

對(duì)于復(fù)雜的Where條件組合,，Where中含有多個(gè)帶索引的字段，考慮用IF語句分情況進(jìn)行討論,；同時(shí),，去掉不必要的外來參數(shù)條件，減低復(fù)雜度,，以便在不同情況下用不同字段上的索引,。

繼續(xù)上面9的例子，對(duì)于包含

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

IF v_DisPosalCourseFlag =1 THEN

Where DisposalCourseFlag = 1 and ....

ELSIF v_DisPosalCourseFlag =2 THEN

Where DisposalCourseFlag = 2 and .... 

,。,。。。,。,。

第十一掌 like子句盡量前端匹配

因?yàn)閘ike參數(shù)使用的非常頻繁，因此如果能夠?qū)ike子句使用索引,，將很高的提高查詢的效率,。

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

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

select * from city where name like ‘S%’

那么查詢的執(zhí)行計(jì)劃將會(huì)變成（INDEX RANGE SCAN）,，成功的利用了name字段的索引,。這意味著Oracle SQL優(yōu)化器會(huì)識(shí)別出用于索引的like子句，只要該查詢的匹配端是具體值,。因此我們?cè)谧鰈ike查詢時(shí),，應(yīng)該盡量使查詢的匹配端是具體值，即使用like ‘S%’,。

第十二掌 用Case語句合并多重掃描

我們常常必須基于多組數(shù)據(jù)表計(jì)算不同的聚集,。例如下例通過三個(gè)獨(dú)立查詢：

例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;

這樣我們需要進(jìn)行三次全表查詢，但是如果我們使用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;         

這樣查詢的結(jié)果一樣,，但是執(zhí)行計(jì)劃只進(jìn)行了一次全表查詢,。

第十三掌 使用nls_date_format

例9：

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

這個(gè)查詢的執(zhí)行計(jì)劃將是全表查詢，如果我們改變nls_date_format,，

SQL>alert session set nls_date_formate=’MM’;

現(xiàn)在重新修改上面的查詢：

select * from record where  ActionTime='12'

這樣就能使用actiontime上的索引了,，它的執(zhí)行計(jì)劃將是（INDEX RANGE SCAN）。

第十四掌 使用基于函數(shù)的索引

前面談到任何對(duì)列的操作都可能導(dǎo)致全表掃描,，例如：

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

但是這種查詢?cè)诳头到y(tǒng)又經(jīng)常使用,，我們可以創(chuàng)建一個(gè)帶有substr函數(shù)的基于函數(shù)的索引，

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

這樣在執(zhí)行上面的查詢語句時(shí),，這個(gè)基于函數(shù)的索引將排上用場(chǎng),，執(zhí)行計(jì)劃將是（INDEX RANGE SCAN）。

第十五掌 基于函數(shù)的索引要求等式匹配

上面的例子中,，我們創(chuàng)建了基于函數(shù)的索引,，但是如果執(zhí)行下面的查詢：

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

得到的執(zhí)行計(jì)劃將還是（TABLE ACCESS FULL），因?yàn)橹挥挟?dāng)數(shù)據(jù)列能夠等式匹配時(shí),，基于函數(shù)的索引才能生效,，這樣對(duì)于這種索引的計(jì)劃和維護(hù)的要求都很高。請(qǐng)注意,，向表中添加索引是非常危險(xiǎn)的操作,，因?yàn)檫@將導(dǎo)致許多查詢執(zhí)行計(jì)劃的變更。然而,，如果我們使用基于函數(shù)的索引就不會(huì)產(chǎn)生這樣的問題,，因?yàn)镺racle只有在查詢使用了匹配的內(nèi)置函數(shù)時(shí)才會(huì)使用這種類型的索引,。

第十六掌 使用分區(qū)索引

在用分析命令對(duì)分區(qū)索引進(jìn)行分析時(shí)，每一個(gè)分區(qū)的數(shù)據(jù)值的范圍信息會(huì)放入Oracle的數(shù)據(jù)字典中,。Oracle可以利用這個(gè)信息來提取出那些只與SQL查詢相關(guān)的數(shù)據(jù)分區(qū),。

例如，假設(shè)你已經(jīng)定義了一個(gè)分區(qū)索引,，并且某個(gè)SQL語句需要在一個(gè)索引分區(qū)中進(jìn)行一次索引掃描,。Oracle會(huì)僅僅訪問這個(gè)索引分區(qū)，而且會(huì)在這個(gè)分區(qū)上調(diào)用一個(gè)此索引范圍的快速全掃描,。因?yàn)椴恍枰L問整個(gè)索引,，所以提高了查詢的速度,。

第十七掌 使用位圖索引

位圖索引可以從本質(zhì)上提高使用了小于1000個(gè)唯一數(shù)據(jù)值的數(shù)據(jù)列的查詢速度,，因?yàn)樵谖粓D索引中進(jìn)行的檢索是在RAM中完成的，而且也總是比傳統(tǒng)的B樹索引的速度要快,。對(duì)于那些少于1000個(gè)唯一數(shù)據(jù)值的數(shù)據(jù)列建立位圖索引,，可以使執(zhí)行效率更快。

第十八掌 決定使用全表掃描還是使用索引

和所有的秘笈一樣,，最后一招都會(huì)又回到起點(diǎn),，最后我們來討論一下是否需要建立索引，也許進(jìn)行全表掃描更快,。在大多數(shù)情況下,，全表掃描可能會(huì)導(dǎo)致更多的物理磁盤輸入輸出，但是全表掃描有時(shí)又可能會(huì)因?yàn)楦叨炔⑿谢拇嬖诙鴪?zhí)行的更快,。如果查詢的表完全沒有順序,，那么一個(gè)要返回記錄數(shù)小于10％的查詢可能會(huì)讀取表中大部分的數(shù)據(jù)塊，這樣使用索引會(huì)使查詢效率提高很多,。但是如果表非常有順序,，那么如果查詢的記錄數(shù)大于40％時(shí)，可能使用全表掃描更快,。因此,，有一個(gè)索引范圍掃描的總體原則是：

1）對(duì)于原始排序的表  僅讀取少于表記錄數(shù)40％的查詢應(yīng)該使用索引范圍掃描。反之,，讀取記錄數(shù)目多于表記錄數(shù)的40％的查詢應(yīng)該使用全表掃描,。

2）對(duì)于未排序的表    僅讀取少于表記錄數(shù)7％的查詢應(yīng)該使用索引范圍掃描。反之,，讀取記錄數(shù)目多于表記錄數(shù)的7％的查詢應(yīng)該使用全表掃描,。

2         總結(jié)

以上的招式，是完全可以相互結(jié)合同時(shí)運(yùn)用的,。而且各種方法之間相互影響,，緊密聯(lián)系,。這種聯(lián)系既存在一致性，也可能帶來沖突,，當(dāng)沖突發(fā)生時(shí),，需要根據(jù)實(shí)際情況進(jìn)行選擇，沒有固定的模式,。最后決定SQL優(yōu)化功力的因素就是對(duì)ORACLE內(nèi)功的掌握程度了,。

另外，值得注意的是：隨著時(shí)間的推移和數(shù)據(jù)的累計(jì)與變化,，ORACLE對(duì)SQL語句的執(zhí)行計(jì)劃也會(huì)改變,，比如：基于代價(jià)的優(yōu)化方法，隨著數(shù)據(jù)量的增大,，優(yōu)化器可能錯(cuò)誤的不選擇索引而采用全表掃描,。這種情況可能是因?yàn)榻y(tǒng)計(jì)信息已經(jīng)過時(shí)，在數(shù)據(jù)量變化很大后沒有及時(shí)分析表,；但如果對(duì)表進(jìn)行分析之后,，仍然沒有用上合理的索引，那么就有必要對(duì)SQL語句用HINT提示,，強(qiáng)制用合理的索引,。但這種HINT提示也不能濫用，因?yàn)檫@種方法過于復(fù)雜,，缺乏通用性和應(yīng)變能力,，同時(shí)也增加了維護(hù)上的代價(jià)；相對(duì)來說,，基于函數(shù)右移,、去掉“IN ，OR ,，<> ,，IS NOT NULL ”、分解復(fù)雜的SQL語句等等方法,，卻是“放之四海皆準(zhǔn)”的,，可以放心大膽的使用。

同時(shí),，優(yōu)化也不是“一勞永逸”的,，必須隨著情況的改變進(jìn)行相應(yīng)的調(diào)整。當(dāng)數(shù)據(jù)庫設(shè)計(jì)發(fā)生變化,，包括更改表結(jié)構(gòu)：字段和索引的增加,、刪除或改名等；業(yè)務(wù)邏輯發(fā)生變化：如查詢方式,、取值范圍發(fā)生改變等等,。在這種情