Oracle語(yǔ)法效率優(yōu)化應(yīng)注意的若干細(xì)節(jié)

1、oracle語(yǔ)法效率優(yōu)化應(yīng)注意的若干細(xì)節(jié)一、盡量避免對(duì)列的操作任何對(duì)列的操作都可能導(dǎo)致全表掃描，這里所謂的操作包括數(shù)據(jù) 庫(kù)函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等式的右邊， 甚至去掉函數(shù)。例：下列sql條件語(yǔ)句中的列都建有恰當(dāng)?shù)乃饕?，?0萬(wàn)行數(shù)據(jù)情 況下執(zhí)行速度卻非常慢：select * from record where substrb(cardno, 1, 4)=，5378，select * from record where amount/30< 1000select * from recordwhere to_char(actiontime,' yyyymmdd

2、，)二'1999120t于where子句中對(duì)列的任何操作結(jié)果都是在sql運(yùn)行時(shí)逐行計(jì)算 得到的，因此它不得不進(jìn)行表掃描，而沒(méi)有使用該列上面的索引；如 果這些結(jié)果在查詢編譯時(shí)就能得到，那么就可以被sql優(yōu)化器優(yōu)化， 使用索引，避免表掃描，因此可將sql修改如下：select * from record where cardno like '5378%'select * from record where amount < 1000*30select * from recordwhere actiontime= to date ('19991201'，

3、yyyymmdd')二、避免不必要的類型轉(zhuǎn)換盡量避免潛在的數(shù)據(jù)類烈轉(zhuǎn)換。如將字符烈數(shù)據(jù)與數(shù)值型數(shù)據(jù)比 較，oracle會(huì)自動(dòng)將字符型用to_number ()函數(shù)進(jìn)行轉(zhuǎn)換，從而導(dǎo)致 全表掃描。例：表tabl中的列coll是字符型(char),則以下語(yǔ)旬存在類型轉(zhuǎn) 換：select col 1,col2 from tabl where coll>10應(yīng)該寫(xiě)為： select coll, col2 from tabl where coll'10三、增加查詢的范圍限制增加查詢的范圍限制，避免全范圍的搜索。例：以卜'查詢表record中時(shí)間actiontime小于2015

4、年5月1日的數(shù)據(jù)：select * from recordwhere actiontime < to date ('20150501'，yyyymm')查詢計(jì)劃表明，上面的查詢對(duì)表進(jìn)行全表掃描，如果我們知道表 屮的最早的數(shù)據(jù)為2011年1月1日，那么，可以增加一個(gè)最小時(shí)間，使 查詢?cè)谝粋€(gè)完整的范圍之內(nèi)。修改女口下： select * from record whereactiontime < to date ('20150501'，yyyymm')and actiontime > to_date ('20110101&#

5、39;，yyyymm')此sql語(yǔ)句將利用actiontime字段上的索引，從而提高查詢效率。 同理，對(duì)于大于某個(gè)值的查詢，如果知道當(dāng)前可能的最大值，也可以 在where子句中加上“and列名max(最大值)”。四、盡量去掉in、0r含有in、0r的where子句常會(huì)使用工作表，使索引失效；如果 不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開(kāi)；拆開(kāi)的子句中應(yīng)該包含索 引。例4： select count (*) from stuff where i d_no(23 秒)可以考慮將or子句分開(kāi)：select count (*) from stuff where id_no二'o's

6、elect count (*) from stuff where id_no二t'然后再做一個(gè)簡(jiǎn)單的加法，與原來(lái)的sql語(yǔ)句相比，查詢速度更快。五、盡量去掉盡量去掉，避免全表掃描，如果數(shù)據(jù)是枚舉值，且取值范圍 固定，則修改為0r方式。例：update servtcetnfo set stateo where stateoo;以上語(yǔ)句由于其中包含了執(zhí)行計(jì)劃中用了全表掃描(table access full),沒(méi)有用到state字段上的索引。實(shí)際應(yīng)用中，市于業(yè) 務(wù)邏輯的限制，字段state為枚舉值，只能等于0, 1或2,而且，值等 于二1, 2的很少，因此可以去掉勺利用索引來(lái)提高效率。修改

7、為：update servtcetnfo set staten where state 二 1 orstate二2 o進(jìn)一步的修改可以參考第4種方法。六、去掉where了句中的is null和is not nullwhere字句中的is null和is not null將不會(huì)使用索引而是進(jìn)行全 表搜索，因此需要通過(guò)改變查詢方式，分情況討論等方法，去掉where 子句中的is null和is not null。七、索引提高數(shù)據(jù)分布不均勻時(shí)查詢效率索引的選擇性低，但數(shù)據(jù)的值分布差異很大時(shí)，仍然可以利用索 引提高效率。表屮數(shù)據(jù)量很大，假設(shè)有一百萬(wàn)行，其屮有一個(gè)字段 flag,取值范

8、圉為枚舉值:0, 1, 2, 3, 4, 5, 6, 7。按照索引建 立的一般規(guī)則，該字段只有8種取值，索引值的重復(fù)率很高，索引選 擇性明顯很低，因此不應(yīng)該建索引。然而，由于該字段上數(shù)據(jù)值的分 布情況非常特殊，具體如下表：取值范圍占總數(shù)據(jù)量的百分比廣51%6 98%7 1%而且，常用的查詢中，查詢flag<6的情況既多又頻繁，毫無(wú)疑問(wèn), 如果能夠建立索引，并且被應(yīng)用，那么將大大提高這種情況的查詢效 率。因此，我們需要在該字段上建立索引。八、利用iiint強(qiáng)制指定索引在oracle優(yōu)化器無(wú)法用上合理索引的情況下，利用hint強(qiáng)制指定索引。繼續(xù)上面七的例子，oracle缺省認(rèn)定，表中列的值是

9、在所有數(shù)據(jù) 行中均勻分布的，也就是說(shuō)，在一百萬(wàn)數(shù)據(jù)量下，每種flag值各有12. 5 萬(wàn)數(shù)據(jù)行與z對(duì)應(yīng)。假設(shè)sql搜索條件flag-2,利用flag列上的索引 進(jìn)行數(shù)據(jù)搜索效率，往往不比全表掃描的高，oracle因此對(duì)索引“視 而不見(jiàn)”，從而在查詢路徑的選擇中，用其他字段上的索引甚至全表 掃描。根據(jù)我們上面的分析，數(shù)據(jù)值的分布很特殊，嚴(yán)重的不均勻。 為了利用索引提高效率，此時(shí)，一方面可以單獨(dú)對(duì)該字段或該表用 analyze語(yǔ)句進(jìn)行分析，對(duì)該列搜集足夠的統(tǒng)計(jì)數(shù)據(jù)，使oracle在查 詢選擇性較高的值時(shí)能用上索引；另一方面，可以利用hint提示，在 select關(guān)鍵字后面，加上“/*+index(

10、表名稱,索引名稱)*/”的方 式，強(qiáng)制oracle優(yōu)化器用上該索引。比女口： select * from serv info where flag=l ;上面的語(yǔ)句，實(shí)際執(zhí)行中oracle用了全表掃描，加上藍(lán)色提示部 分后，用到索引查詢。如下：select /*+ index(serv_info, idx1_ flag) */ *from serv_info where flag=l:請(qǐng)注意，這種方法會(huì)加大代碼維護(hù)的難度，而且該字段上索引的 名稱被改變之后，必須要同步所有指定索引的hint代碼，否則hint 提示將被oracle忽略掉。九、屏蔽無(wú)用索引繼續(xù)上面8的例子，由于實(shí)際查詢中，還有涉及

11、到flag=6的查詢， 而此時(shí)如果用上該字段上的索引，將是非常不明智的，效率也極低。 因此這種情況下，我們需要用特殊的方法屏蔽該索引，以便oracle 選擇其他字段上的索引。比如，如果字段為數(shù)值型的就在表達(dá)式的字 段名后，添加“+ 0” ,為字符型的就并上空串：“丨丨”如： select * from servinfo where flag+ 0=6不過(guò)，不要把該用的索引屏蔽掉了，否則同樣會(huì)產(chǎn)生低效率的全 表掃描。十、分解復(fù)雜查詢，用常量代替變量對(duì)于復(fù)朵的where條件組合where中含有多個(gè)帶索引的字段，考 慮用if語(yǔ)句分情況進(jìn)行討論；同時(shí)，去掉不必要的外來(lái)參數(shù)條件，減 低復(fù)雜度，以便在不同

12、情況下用不同字段上的索引。繼續(xù)上面9的例子，對(duì)于包含where (flag < v_flag) or (v_flag is null) and 的查詢, (這里v.flag為一個(gè)輸入變量，取值范圍可能為null, 0, 1, 2, 3, 4, 5, 6, 7),可以考慮分情況用if語(yǔ)句進(jìn)行討論，類似：if v_flag 二1 thenwhere flag = 1 and .elsif v_flag =2 thenwhere flag = 2 and .十一、like子句盡量前端匹配因?yàn)閘ike參數(shù)使用的非常頻繁，因此如果能夠?qū)ike子句使用索 引，將很高的提高查詢的效率。例：selec

13、t * 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)該盡量使查詢的匹配端是具體值，即使用likeo十二、用case語(yǔ)句合并多重掃描我們常常必須基于多組數(shù)據(jù)表計(jì)算不同的

14、聚集。例如下例通過(guò)三 個(gè)獨(dú)立查詢：例：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語(yǔ)句：select count (sale when sal <1000then 1 else null end) countpoor,count (sale when between 100

15、0 and 5000then 1 else null end) count blue collar,count (sale when sal >5000then 1 else null end) count_poorfrom emp;這樣查詢的結(jié)果一樣，但是執(zhí)行計(jì)劃只進(jìn)行了一次全表查詢。十三、使用 n 1 s_date_format例：select * from record where to char (actiontime,' mm')二'12' 這個(gè)查詢的執(zhí)行計(jì)劃將是全表查詢，如果我們改變 nls_date_format,sql>alert s

16、ession set nls_date_formate=, mm'現(xiàn)在重新修改上面的查詢：select * from record where actiontime二'12'這樣就能使用actiontime上的索引了。十四、使用基于函數(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 ( subs

17、tr(ename, 1, 2);這樣在執(zhí)行上面的查詢語(yǔ)句時(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)生這樣的問(wèn)題，因?yàn)閛racle只有在查詢使用了 匹配的內(nèi)置函數(shù)時(shí)才會(huì)使用這種類型的索引。十六、使用分區(qū)索引在用分析命令對(duì)分區(qū)索引進(jìn)行分析時(shí)，每一個(gè)分區(qū)的數(shù)據(jù)值的范 圉信息會(huì)放入oracle的數(shù)據(jù)字典中。oracle可