oracle語(yǔ)句優(yōu)化30個(gè)規(guī)則詳解

1、1. 選用適合的Oracle優(yōu)化器Oracle的優(yōu)化器共有3種：a. RULE (基于規(guī)則)b. COOST (基基于成本)c. CHHOOSE (選擇性)設(shè)置缺省的的優(yōu)化器，可可以通過(guò)對(duì)iinit.oora文件中中OPTIMMIZER_MODE參參數(shù)的各種聲聲明，如RUULE，COOST，CHHOOSE，AALL_ROOWS，F(xiàn)IIRST_RROWS . 你當(dāng)然也也在SQL句句級(jí)或是會(huì)話(huà)話(huà)(sesssion)級(jí)級(jí)對(duì)其進(jìn)行覆覆蓋。為了使用基基于成本的優(yōu)優(yōu)化器(CBBO， Coost-Baased OOptimiizer) ， 你必須須經(jīng)常運(yùn)行aanalyzze 命令，以以增加數(shù)據(jù)庫(kù)庫(kù)中的對(duì)象統(tǒng)

2、統(tǒng)計(jì)信息(oobjectt stattisticcs)的準(zhǔn)確確性。如果數(shù)據(jù)庫(kù)庫(kù)的優(yōu)化器模模式設(shè)置為選選擇性(CHHOOSE)，那么實(shí)際際的優(yōu)化器模模式將和是否否運(yùn)行過(guò)annalyzee命令有關(guān)。 如果tabble已經(jīng)被被analyyze過(guò)， 優(yōu)化器模式式將自動(dòng)成為為CBO ， 反之，數(shù)據(jù)據(jù)庫(kù)將采用RRULE形式式的優(yōu)化器。在缺省情況況下，ORAACLE采用用CHOOSSE優(yōu)化器，為為了避免那些些不必要的全全表掃描(ffull ttable scan) ， 你必必須盡量避免免使用CHOOOSE優(yōu)化化器，而直接接采用基于規(guī)規(guī)則或者基于于成本的優(yōu)化化器。2. 訪問(wèn)問(wèn)Tablee的方式Orracle

3、采用兩種訪訪問(wèn)表中記錄錄的方式：a. 全表表掃描全表掃描就就是順序地訪訪問(wèn)表中每條條記錄。 OORACLEE采用一次讀讀入多個(gè)數(shù)據(jù)據(jù)塊(dattabasee blocck)的方式式優(yōu)化全表掃掃描。b. 通過(guò)過(guò)ROWIDD訪問(wèn)表你可以采用用基于ROWWID的訪問(wèn)問(wèn)方式情況，提提高訪問(wèn)表的的效率， RROWID包包含了表中記記錄的物理位位置信息ORACLLE采用索引引(INDEEX)實(shí)現(xiàn)了了數(shù)據(jù)和存放放數(shù)據(jù)的物理理位置(ROOWID)之之間的聯(lián)系。通通常索引提供供了快速訪問(wèn)問(wèn)ROWIDD的方法，因因此那些基于于索引列的查查詢(xún)就可以得得到性能上的的提高。3. 共享享SQL語(yǔ)句句為了不重復(fù)復(fù)解析相同的

4、的SQL語(yǔ)句句，在第一次次解析之后， ORACLLE將SQLL語(yǔ)句存放在在內(nèi)存中。這這塊位于系統(tǒng)統(tǒng)全局區(qū)域SSGA(syystem globaal areea)的共享享池(shaared bbufferr pooll)中的內(nèi)存存可以被所有有的數(shù)據(jù)庫(kù)用用戶(hù)共享。 因此，當(dāng)你你執(zhí)行一個(gè)SSQL語(yǔ)句(有時(shí)被稱(chēng)為為一個(gè)游標(biāo))時(shí)，如果它它和之前的執(zhí)執(zhí)行過(guò)的語(yǔ)句句完全相同， ORACLLE就能很快快獲得已經(jīng)被被解析的語(yǔ)句句以及最好的的執(zhí)行路可惜的是ORAACLE只對(duì)對(duì)簡(jiǎn)單的表提提供高速緩沖沖(cachhe buffferinng) ，這這個(gè)功能并不不適用于多表表連接查詢(xún)。數(shù)據(jù)庫(kù)管理理員必須在iinit.o

5、ora中為這這個(gè)區(qū)域設(shè)置置合適的參數(shù)數(shù)，當(dāng)這個(gè)內(nèi)內(nèi)存區(qū)域越大大，就可以保保留更多的語(yǔ)語(yǔ)句，當(dāng)然被被共享的可能能性也就越大大了。當(dāng)你向ORRACLE 提交一個(gè)SSQL語(yǔ)句，OORACLEE會(huì)首先在這這塊內(nèi)存中查查找相同的語(yǔ)語(yǔ)句。這里需要注注明的是，OORACLEE對(duì)兩者采取取的是一種嚴(yán)嚴(yán)格匹配，要要達(dá)成共享，SSQL語(yǔ)句必必須完全相同同(包括空格格，換行等)。共享的語(yǔ)句句必須滿(mǎn)足三三個(gè)條件：A. 字符符級(jí)的比較：當(dāng)前被執(zhí)行行的語(yǔ)句和共共享池中的語(yǔ)語(yǔ)句必須完全全相同。例如：SELECTT*FROMMEMP;和下列每一一個(gè)都不同SELEECT*frromEMPP;Seleect*FrromEmpp;

6、SELEECT*FRROMEMPP;B. 兩個(gè)個(gè)語(yǔ)句所指的的對(duì)象必須完完全相同：例如：用戶(hù) 對(duì)象象名 如何訪訪問(wèn) Jackksal_llimitpprivattesynoonymWoork_ciitypubblicsyynonymmPlantt_detaailpubblicsyynonymmJillssal_liimitprrivateesynonnymWork_ccitypuublicssynonyymPlannt_dettailtaableowwner考慮一下下下列SQL語(yǔ)語(yǔ)句能否在這這兩個(gè)用戶(hù)之之間共享。SQL 能能否共享 原原因selecct maxx(sal_cap) from sa

7、l_llimit; 不能 每每個(gè)用戶(hù)都有有一個(gè)priivate synonnym - sal_llimit , 它們是是不同的對(duì)象象selecct couunt(*00 fromm workk_cityy wherre sdeesc liike NNEW%; 能 兩個(gè)個(gè)用戶(hù)訪問(wèn)相相同的對(duì)象ppublicc synoonym - workk_cityyselecct a.ssdesc,b.loccationn fromm workk_cityy a , plantt_detaail b wheree a.ciity_idd = b.city_id 不能能 用戶(hù)jaack 通過(guò)過(guò)privaate

8、syynonymm訪問(wèn)plaant_deetail 而jilll 是表的所所有者,對(duì)象象不同.C. 兩個(gè)SQQL語(yǔ)句中必必須使用相同同的名字的綁綁定變量(bbind vvariabbles)例如：第一一組的兩個(gè)SSQL語(yǔ)句是是相同的(可可以共享)，而而第二組中的的兩個(gè)語(yǔ)句是是不同的(即即使在運(yùn)行時(shí)時(shí)，賦于不同同的綁定變量量相同的值)a.selectppin，naamefroompeopplewheerepinn=：blkk1.pinn;seleectpinn，nameefromppeopleewhereepin=：blk1.pin;b.selectppin，naamefroompeopplew

9、heerepinn=：blkk1.ot_ind;sselecttpin，nnamefrrompeooplewhherepiin=：bllk1.ovv_ind;4. 選擇擇最有效率的的表名順序(只在基于規(guī)規(guī)則的優(yōu)化器器中有效)ORACLLE的解析器器按照從右到到左的順序處處理FROMM子句中的表表名，因此FFROM子句句中寫(xiě)在最后后的表(基礎(chǔ)礎(chǔ)表 driiving tablee)將被最先先處理。 在在FROM子子句中包含多多個(gè)表的情況況下，你必須須選擇記錄條條數(shù)最少的表表作為基礎(chǔ)表表。當(dāng)ORAACLE處理理多個(gè)表時(shí)，會(huì)會(huì)運(yùn)用排序及及合并的方式式連接它們。首首先，掃描第第一個(gè)表(FFROM子句句中

10、最后的那那個(gè)表)并對(duì)對(duì)記錄進(jìn)行派派序，然后掃掃描第二個(gè)表表(FROMM子句中最后后第二個(gè)表)，最后將所所有從第二個(gè)個(gè)表中檢索出出的記錄與第第一個(gè)表中合合適記錄進(jìn)行行合并。例如：表 TABB1 16，3384 條記記錄表 TABB2 1 條條記錄選擇TABB2作為基礎(chǔ)礎(chǔ)表 (最好好的方法)selectccount(*)froomtab11，tab22執(zhí)行時(shí)間00.96秒選擇TABB2作為基礎(chǔ)礎(chǔ)表 (不佳佳的方法)selectccount(*)froomtab22，tab11執(zhí)行時(shí)間226.09秒秒如果有3個(gè)個(gè)以上的表連連接查詢(xún)， 那就需要選選擇交叉表(interrsectiion taable)

11、作作為基礎(chǔ)表， 交叉表是指指那個(gè)被其他他表所引用的的表。例如： EEMP表描述述了LOCAATION表表和CATEEGORY表表的交集。SELECCT*FROOMLOCAATIONLL,CATEEGORYCC,EMPEEWHEREEE.EMPP_NOBEETWEENN1000AAND20000ANDEE.CAT_NO=C.CAT_NNOANDEE.LOCNN=L.LOOCN將比下列SQLL更有效率 SELEECT*FRROMEMPPE,LOCCATIONNL,CATTEGORYYCWHERREE.CAAT_NO=C.CATT_NOANNDE.LOOCN=L.LOCNAANDE.EEMP_NOO

12、BETWEEEN10000AND220005. WHHERE子句句中的連接順順序。ORACLLE采用自下下而上的順序序解析WHEERE子句，根根據(jù)這個(gè)原理理，表之間的的連接必須寫(xiě)寫(xiě)在其他WHHERE條件件之前， 那那些可以過(guò)濾濾掉最大數(shù)量量記錄的條件件必須寫(xiě)在WWHERE子子句的末尾。例如：（低效，執(zhí)執(zhí)行時(shí)間1556.3秒）SELECCTFROOMEMPEEWHEREESAL550000AANDJOBB=MANNAGERAND255(SELLECTCOOUNT(*)FROMMEMPWHHEREMGGR=E.EEMPNO);(高效,執(zhí)執(zhí)行時(shí)間100.6秒)SELEECTFRROMEMPPEWHE

13、RRE25500000ANNDJOB=MANAAGER;6. SEELECT子子句中避免使使用 * 當(dāng)你想在SSELECTT子句中列出出所有的COOLUMN時(shí)時(shí)，使用動(dòng)態(tài)態(tài)SQL列引引用 * 是一個(gè)方方便的方法。不不幸的是，這這是一個(gè)非常常低效的方法法。實(shí)際上，OORACLEE在解析的過(guò)過(guò)程中， 會(huì)會(huì)將* 依次轉(zhuǎn)換成成所有的列名名， 這個(gè)工工作是通過(guò)查查詢(xún)數(shù)據(jù)字典典完成的， 這意味著將將耗費(fèi)更多的的時(shí)間。7. 減少少訪問(wèn)數(shù)據(jù)庫(kù)庫(kù)的次數(shù)當(dāng)執(zhí)行每條條SQL語(yǔ)句句時(shí)， ORRACLE在在內(nèi)部執(zhí)行了了許多工作： 解析SQQL語(yǔ)句， 估算索引的的利用率， 綁定變量 ， 讀數(shù)據(jù)據(jù)塊等等。 由此可見(jiàn)， 減少

14、訪問(wèn)數(shù)數(shù)據(jù)庫(kù)的次數(shù)數(shù) ， 就能能實(shí)際上減少少ORACLLE的工作量量。例如，以下下有三種方法法可以檢索出出雇員號(hào)等于于0342或或0291的的職員。方法1 (最低效)SELEECTEMPP_NAMEE,SALAARY,GRRADEFRROMEMPPWHEREEEMP_NNO=3422;SELEECTEMPP_NAMEE,SALAARY,GRRADEFRROMEMPPWHEREEEMP_NNO=2911;方法2 (次低低效)DECLAARECURRSORC11(E_NOONUMBEER)ISSSELECTTEMP_NNAME,SSALARYY,GRADDEFROMMEMPWHHEREEMMP_N

15、O=E_NO;BEGINNOPENCC1(3422);FETTCHC1IINTO,.,.;OPENNC1(2991);FEETCHC11INTO,.,.;CLOOSEC1; END;方法3 (高效)SELEECTA.EEMP_NAAME,A.SALARRY,A.GGRADE,B.EMPP_NAMEE,B.SAALARY,B.GRAADEFROOMEMPAA,EMPBBWHEREEA.EMPP_NO=3342ANDDB.EMPP_NO=2291;注意：在SQL*Plus ， SQLL*Formms和Proo*C中重新新設(shè)置ARRRAYSIZZE參數(shù)， 可以增加每每次數(shù)據(jù)庫(kù)訪訪問(wèn)的檢索數(shù)數(shù)據(jù)量 ，

16、建建議值為2000.8. 使用用DECODDE函數(shù)來(lái)減減少處理時(shí)間間使用DECCODE函數(shù)數(shù)可以避免重重復(fù)掃描相同同記錄或重復(fù)復(fù)連接相同的的表。例如： SELEECTCOUUNT(*)，SUM(SAL)FFROMEEMPWHEEREDEPPT_NO=0020AANDENAAMELIKKESMMITH%;SELEECTCOUUNT(*)，SUM(SAL)FFROMEEMPWHEEREDEPPT_NO=0030AANDENAAMELIKKESMMITH%;你可以用DDECODEE函數(shù)高效地地得到相同結(jié)結(jié)果SELECCTCOUNNT(DECCODE(DDEPT_NNO,00220,X,NULLL)D

17、00020_COOUNT,CCOUNT(DECODDE(DEPPT_NO,0030,X,NNULL)D00300_COUNNT,SUMM(DECOODE(DEEPT_NOO,00200,SAL,NULL)D00220_SALL,SUM(DECODDE(DEPPT_NO,0030,SAL,NNULL)D00300_SALFFROMEMMPWHERREENAMMELIKEESMITTH%;類(lèi)似的，DDECODEE函數(shù)也可以以運(yùn)用于GRROUP BBY 和ORRDER BBY子句中。9. 整合合簡(jiǎn)單，無(wú)關(guān)關(guān)聯(lián)的數(shù)據(jù)庫(kù)庫(kù)訪問(wèn)如果你有幾幾個(gè)簡(jiǎn)單的數(shù)數(shù)據(jù)庫(kù)查詢(xún)語(yǔ)語(yǔ)句，你可以以把它們整合合到一個(gè)查詢(xún)?cè)冎?即

18、使它它們之間沒(méi)有有關(guān)系)例如：SELEECTNAMMEFROMMEMPWHHEREEMMP_NO=1234;SELECCTNAMEEFROMDDPTWHEEREDPTT_NO=110;SELLECTNAAMEFROOMCATWWHERECCAT_TYYPE=RRD;上面的3個(gè)個(gè)查詢(xún)可以被被合并成一個(gè)個(gè)： SELEECTE.NNAME,DD.NAMEE,C.NAAMEFROOMCATCC,DPTDD,EMPEE,DUALLXWHERRENVL(X,XX.DUMMMY)=NVVL(X,E.ROOWID(+)ANDDNVL(X,X.DUMMYY)=NVLL(X,D.ROWWID(+)ANDNNVL(

19、XX,X.DDUMMY)=NVL(X,CC.ROWIID(+)ANDE.EMP_NNO(+)=1234AANDD.DDEPT_NNO(+)=10ANDDC.CATT_TYPEE(+)=RD;10. 刪刪除重復(fù)記錄錄最高效的刪刪除重復(fù)記錄錄方法 ( 因?yàn)槭褂昧肆薘OWIDD) DELEETEFROOMEMPEEWHEREEE.ROWWID(SSELECTTMIN(XX.ROWIID)FROOMEMPXXWHEREEX.EMPP_NO=EE.EMP_NO);11. 用用TRUNCCATE替代代DELETTE當(dāng)刪除表中中的記錄時(shí)，在在通常情況下下， 回滾段段(rolllback segmeents

20、) 用來(lái)存放放可以被恢復(fù)復(fù)的信息。 如果你沒(méi)有有COMMIIT事務(wù)，OORACLEE會(huì)將數(shù)據(jù)恢恢復(fù)到刪除之之前的狀態(tài)(準(zhǔn)確地說(shuō)是是恢復(fù)到執(zhí)行行刪除命令之之前的狀況)而當(dāng)運(yùn)用TTRUNCAATE時(shí)， 回滾段不再再存放任何可可被恢復(fù)的信信息。當(dāng)命令令運(yùn)行后，數(shù)數(shù)據(jù)不能被恢恢復(fù)。因此很很少的資源被被調(diào)用，執(zhí)行行時(shí)間也會(huì)很很短。(譯者按： TRUNNCATE只只在刪除全表表適用，TRRUNCATTE是DDLL不是DMLL)12. 盡盡量多使用CCOMMITT只要有可能能，在程序中中盡量多使用用COMMIIT， 這樣樣程序的性能能得到提高，需需求也會(huì)因?yàn)闉镃OMMIIT所釋放的的資源而減少少：COMMM

21、IT所釋放放的資源：a. 回滾滾段上用于恢恢復(fù)數(shù)據(jù)的信信息。b. 被程序語(yǔ)語(yǔ)句獲得的鎖鎖c. reedo loog bufffer 中中的空間d. ORRACLE為為管理上述33種資源中的的內(nèi)部花費(fèi)(譯者按： 在使用CCOMMITT時(shí)必須要注注意到事務(wù)的的完整性，現(xiàn)現(xiàn)實(shí)中效率和和事務(wù)完整性性往往是魚(yú)和和熊掌不可得得兼)13. 計(jì)計(jì)算記錄條數(shù)數(shù)和一般的觀觀點(diǎn)相反， countt(*) 比比countt(1)稍快快 ， 當(dāng)然然如果可以通通過(guò)索引檢索索，對(duì)索引列列的計(jì)數(shù)仍舊舊是最快的。 例如 COOUNT(EEMPNO)(譯者按： 在CSDDN論壇中，曾曾經(jīng)對(duì)此有過(guò)過(guò)相當(dāng)熱烈的的討論， 作作者的觀點(diǎn)

22、并并不十分準(zhǔn)確確，通過(guò)實(shí)際際的測(cè)試，上上述三種方法法并沒(méi)有顯著著的性能差別別)14. 用用Wheree子句替換HHAVINGG子句避免使用HHAVINGG子句， HHAVINGG 只會(huì)在檢檢索出所有記記錄之后才對(duì)對(duì)結(jié)果集進(jìn)行行過(guò)濾。 這這個(gè)處理需要要排序，總計(jì)計(jì)等操作。 如果能通過(guò)過(guò)WHEREE子句限制記記錄的數(shù)目，那那就能減少這這方面的開(kāi)銷(xiāo)銷(xiāo)。例如：低效：SELECCTREGIION，AVVG(LOGG_SIZEE)FROMMLOCATTIONGRROUPBYYREGIOONHAVIINGREGGIONREEGION!=SYDDNEYANDREEGION!=PERRTH高效：SELEECTR

23、EGGION，AAVG(LOOG_SIZZE)FROOMLOCAATIONWWHERERREGIONNREGIOON!=SSYDNEYYANDRREGIONN!=PEERTHGGROUPBBYREGIION15. 減減少對(duì)表的查查詢(xún)?cè)诤凶硬椴樵?xún)的SQLL語(yǔ)句中，要要特別注意減減少對(duì)表的查查詢(xún)。例如：低效SELEECTTABB_NAMEEFROMTTABLESSWHEREETAB_NNAME=(SELECCTTAB_NAMEFFROMTAAB_COLLUMNSWHHEREVEERSIONN=604)ANDDDB_VERR=(SELLECTDBB_VERFFROMTAAB_COLLUMNSWWH

24、EREVVERSIOON=6044)高效SELEECTTABB_NAMEEFROMTTABLESSWHEREE(TAB_NAME,DB_VEER)=(SSELECTTTAB_NNAME,DDB_VERR)FROMMTAB_CCOLUMNNSWHERREVERSSION=6604)Uppdate多多個(gè)Coluumn例子：低效：UPDAATEEMPPSETEMMP_CATT=(SELLECTMAAX(CATTEGORYY)FROMMEMP_CCATEGOORIES),SAL_RANGEE=(SELLECTMAAX(SALL_RANGGE)FROOMEMP_CATEGGORIESS)WHERREEM

25、P_DEPT=0020;高效： UPDAATEEMPPSET(EEMP_CAAT,SALL_RANGGE)=(SSELECTTMAX(CCATEGOORY),MMAX(SAAL_RANNGE)FRROMEMPP_CATEEGORIEES)WHEEREEMPP_DEPTT=00200;16. 通通過(guò)內(nèi)部函數(shù)數(shù)提高SQLL效率。SELEECTH.EEMPNO,E.ENAAME,H.HIST_TYPE,T.TYPPE_DESSC,COUUNT(*)FROMHHISTORRY_TYPPET,EMMPE,EMMP_HISSTORYHHWHEREEH.EMPPNO=E.EMPNOOANDH.HIST_TY

26、PE=T.HISST_TYPPEGROUUPBYH.EMPNOO,E.ENNAME,HH.HISTT_TYPEE,T.TYYPE_DEESC;通過(guò)調(diào)用下下面的函數(shù)可可以提高效率率。FUNCCTIONLLOOKUPP_HISTT_TYPEE(TYPIINNUMBBER)REETURNVVARCHAAR2ASTDESSCVARCCHAR2(30);CCURSORRC1ISSSELECTTTYPE_DESCFFROMHIISTORYY_TYPEEWHEREEHIST_TYPE=TYP;BBEGINOOPENC11;FETCCHC1INNTOTDEESC;CLLOSEC11;RETUURN(NVVL(

27、TDEESC,?);EEND;FUUNCTIOONLOOKKUP_EMMP(EMPPINNUMMBER)RRETURNNVARCHHAR2ASSENAMEEVARCHHAR2(330);CUURSORCC1ISSEELECTEENAMEFFROMEMMPWHERREEMPNNO=EMPP;BEGIINOPENNC1;FEETCHC11INTOEENAME;CLOSEEC1;REETURN(NVL(EENAME,?);END;SELECCTH.EMMPNO,LLOOKUPP_EMP(H.EMPPNO),HH.HISTT_TYPEE,LOOKKUP_HIIST_TYYPE(H.HIST_TYPE)

28、,COUNNT(*)FFROMEMMP_HISSTORYHHGROUPPBYH.EEMPNO,H.HISST_TYPPE;(譯者按： 經(jīng)常在論論壇中看到如如 能不能能用一個(gè)SQQL寫(xiě)出。 的貼子， 殊不知復(fù)雜雜的SQL往往往犧牲了執(zhí)執(zhí)行效率。 能夠掌握上上面的運(yùn)用函函數(shù)解決問(wèn)題題的方法在實(shí)實(shí)際工作中是是非常有意義義的)低效：UPDAATEEMPPSETEMMP_CATT=(SELLECTMAAX(CATTEGORYY)FROMMEMP_CCATEGOORIES),SAL_RANGEE=(SELLECTMAAX(SALL_RANGGE)FROOMEMP_CATEGGORIESS)WHERREEM

29、P_DEPT=0020;高效： UPDAATEEMPPSET(EEMP_CAAT,SALL_RANGGE)=(SSELECTTMAX(CCATEGOORY),MMAX(SAAL_RANNGE)FRROMEMPP_CATEEGORIEES)WHEEREEMPP_DEPTT=00200;16. 通通過(guò)內(nèi)部函數(shù)數(shù)提高SQLL效率。SELEECTH.EEMPNO,E.ENAAME,H.HIST_TYPE,T.TYPPE_DESSC,COUUNT(*)FROMHHISTORRY_TYPPET,EMMPE,EMMP_HISSTORYHHWHEREEH.EMPPNO=E.EMPNOOANDH.HIST_TY

30、PE=T.HISST_TYPPEGROUUPBYH.EMPNOO,E.ENNAME,HH.HISTT_TYPEE,T.TYYPE_DEESC;通過(guò)調(diào)用下下面的函數(shù)可可以提高效率率。FUNCCTIONLLOOKUPP_HISTT_TYPEE(TYPIINNUMBBER)REETURNVVARCHAAR2ASTDESSCVARCCHAR2(30);CCURSORRC1ISSSELECTTTYPE_DESCFFROMHIISTORYY_TYPEEWHEREEHIST_TYPE=TYP;BBEGINOOPENC11;FETCCHC1INNTOTDEESC;CLLOSEC11;RETUURN(NVVL(

31、TDEESC,?);EEND;FUUNCTIOONLOOKKUP_EMMP(EMPPINNUMMBER)RRETURNNVARCHHAR2ASSENAMEEVARCHHAR2(330);CUURSORCC1ISSEELECTEENAMEFFROMEMMPWHERREEMPNNO=EMPP;BEGIINOPENNC1;FEETCHC11INTOEENAME;CLOSEEC1;REETURN(NVL(EENAME,?);END;SELECCTH.EMMPNO,LLOOKUPP_EMP(H.EMPPNO),HH.HISTT_TYPEE,LOOKKUP_HIIST_TYYPE(H.HIST_TYPE)

32、,COUNNT(*)FFROMEMMP_HISSTORYHHGROUPPBYH.EEMPNO,H.HISST_TYPPE;(譯者按： 經(jīng)常在論論壇中看到如如 能不能能用一個(gè)SQQL寫(xiě)出。 的貼子， 殊不知復(fù)雜雜的SQL往往往犧牲了執(zhí)執(zhí)行效率。 能夠掌握上上面的運(yùn)用函函數(shù)解決問(wèn)題題的方法在實(shí)實(shí)際工作中是是非常有意義義的)(方法二： 最最高效)SELEECT.FFROMEMMPEWHEERENOTTEXISTTS(SELLECTXXFROMMDEPTDDWHEREED.DEPPT_NO=E.DEPPT_NOAANDDEPPT_CATT=A);20. 用用表連接替換換EXISTTS通常來(lái)說(shuō) ， 采用

33、表表連接的方式式比EXISSTS更有效效率SELECCTENAMMEFROMMEMPEWWHEREEEXISTSS(SELEECTXFROMDDEPTWHHEREDEEPT_NOO=E.DEEPT_NOOANDDEEPT_CAAT=A);(更高效)SELEECTENAAMEFROOMDEPTTD,EMPPEWHERREE.DEEPT_NOO=D.DEEPT_NOOANDDEEPT_CAAT=A;(譯者按： 在RBOO的情況下，前前者的執(zhí)行路路徑包括FIILTER，后后者使用NEESTED LOOP)21. 用用EXISTTS替換DIISTINCCT當(dāng)提交一個(gè)個(gè)包含一對(duì)多多表信息(比比如部門(mén)表和

34、和雇員表)的的查詢(xún)時(shí)，避避免在SELLECT子句句中使用DIISTINCCT. 一般般可以考慮用用EXISTT替換例如：低效： SELEECTDISSTINCTTDEPT_NO,DEEPT_NAAMEFROOMDEPTTD,EMPPEWHERRED.DEEPT_NOO=E.DEEPT_NOO高效：SELECCTDEPTT_NO,DDEPT_NNAMEFRROMDEPPTDWHEEREEXIISTS(SSELECTTXFRROMEMPPEWHERREE.DEEPT_NOO=D.DEEPT_NOO);EXISTTS 使查詢(xún)?cè)兏鼮檠杆?，因因?yàn)镽DBMMS核心模塊塊將在子查詢(xún)?cè)兊臈l件一旦旦滿(mǎn)足后，立立

35、刻返回結(jié)果果。22. 識(shí)識(shí)別低效執(zhí)執(zhí)行的SQQL語(yǔ)句用下列SQQL工具找出出低效SQLL：SELECCTEXECCUTIONNS,DISSK_REAADS,BUUFFER_GETS,ROUNDD(BUFFFER_GGETS-DDISK_RREADS)/BUFFFER_GEETS,2)Hit_rradio,ROUNDD(DISKK_READDS/EXEECUTIOONS,2)Readss_per_run,SSQL_TEEXTFROOMV$SQQLAREAAWHEREEEXECUUTIONSS0ANDDBUFFEER_GETTS0ANND(BUFFFER_GGETS-DDISK_RREADS)/B

36、UFFFER_GEETSllist1SSELECTT*2FROOMdeptt,emp33*WHERREemp.deptnno=deppt.depptnoSQQLsettautottracettraceoonly/*traceeonly可可以不顯示執(zhí)執(zhí)行結(jié)果*/SQL/14rowwsseleected.ExecuutionPPlan-0SELLECTSTTATEMEENTptiimizerr=CHOOOSE10NNESTEDDLOOPSS21TABBLEACCCESS(FFULL)OOFEMPP31TAABLEACCCESS(BYINDDEXROWWID)OFFDEPTT43INNDEX(UUN

37、IQUEESCAN)OFPKK_DEPTT(UNIIQUE)SStatisstics-0reecursiivecallls2dbbblockkgets330conssistenntgetss0physsicalrreads00redossize25598byttessenntviaSSQL*Neettocllient5503byttesrecceiveddviaSQQL*Nettfromcclientt2SQL*Netrooundtrripstoo/frommcliennt0sorrts(meemory)0sortts(dissk)14rrowsprrocesssed通過(guò)以上分分析，可以得得出

38、實(shí)際的執(zhí)執(zhí)行步驟是： 1.TAABLEACCCESS(FULL)OFEMMP2.IINDEX(UNIQUUESCANN)OFPPK_DEPPT(UNNIQUE)3.TABBLEACCCESS(BBYINDEEXROWIID)OFDEPT4.NESSTEDLOOOPS(JJOININNG1ANDD3)注： 目前前許多第三方方的工具如TTOAD和OORACLEE本身提供的的工具如OMMS的SQLL Anallyze都提提供了極其方方便的EXPPLAIN PLAN工工具。也許喜喜歡圖形化界界面的朋友們們可以選用它它們。25. 用用索引提高效效率索引是表的的一個(gè)概念部部分，用來(lái)提提高檢索數(shù)據(jù)據(jù)的效率。

39、 實(shí)際上，OORACLEE使用了一個(gè)個(gè)復(fù)雜的自平平衡B-trree結(jié)構(gòu)。通通常，通過(guò)索索引查詢(xún)數(shù)據(jù)據(jù)比全表掃描描要快。 當(dāng)當(dāng)ORACLLE找出執(zhí)行行查詢(xún)和Uppdate語(yǔ)語(yǔ)句的最佳路路徑時(shí)， OORACLEE優(yōu)化器將使使用索引。同同樣在聯(lián)結(jié)多多個(gè)表時(shí)使用用索引也可以以提高效率。 另一個(gè)使用用索引的好處處是，它提供供了主鍵(pprimarry keyy)的唯一性性驗(yàn)證。踐， 感到還是是用SQLPPLUS中的的SET TTRACE 功能比較方方便。除了那些LONNG或LONNG RAWW數(shù)據(jù)類(lèi)型， 你可以索引引幾乎所有的的列。 通常常， 在大型型表中使用索索引特別有效效。 當(dāng)然，你你也會(huì)發(fā)現(xiàn)， 在

40、掃描小表表時(shí)，使用索索引同樣能提提高效率。雖然使用索索引能得到查查詢(xún)效率的提提高，但是我我們也必須注注意到它的代代價(jià)。 索引引需要空間來(lái)來(lái)存儲(chǔ)，也需需要定期維護(hù)護(hù)，每當(dāng)有記記錄在表中增增減或索引列列被修改時(shí)， 索引本身也也會(huì)被修改。 這意味著每每條記錄的IINSERTT ， DEELETE ， UPDDATE將為為此多付出44 ， 5 次的磁盤(pán)II/O . 因?yàn)樗饕栊枰~外的存存儲(chǔ)空間和處處理，那些不不必要的索引引反而會(huì)使查查詢(xún)反應(yīng)時(shí)間間變慢。譯者按：定定期的重構(gòu)索索引是有必要要的。ALTERR INDEEX REBBUILD26. 索索引的操作ORACLLE對(duì)索引有有兩種訪問(wèn)模模式。索引唯

41、一掃掃描 ( IINDEX UNIQUUE SCAAN)大多數(shù)情況況下， 優(yōu)化化器通過(guò)WHHERE子句句訪問(wèn)INDDEX.例如：表LODGGING有兩兩個(gè)索引 ： 建立在LLODGINNG列上的唯唯一性索引LLODGINNG_PK和和建立在MAANAGERR列上的非唯唯一性索引LLODGINNG$MANNAGER.SELEECT*FRROMLODDGINGWWHERELLODGINNG=ROOSEHILLL;在內(nèi)部 ， 上述SQLL將被分成兩兩步執(zhí)行， 首先 ， LODGIING_PKK 索引將通通過(guò)索引唯一一掃描的方式式被訪問(wèn) ， 獲得相對(duì)應(yīng)應(yīng)的ROWIID， 通過(guò)過(guò)ROWIDD訪問(wèn)表的方方

42、式執(zhí)行下一一步檢索。如果被檢索索返回的列包包括在INDDEX列中，OORACLEE將不執(zhí)行第第二步的處理理(通過(guò)ROOWID訪問(wèn)問(wèn)表)。 因因?yàn)闄z索數(shù)據(jù)據(jù)保存在索引引中， 單單單訪問(wèn)索引就就可以完全滿(mǎn)滿(mǎn)足查詢(xún)結(jié)果果。下面SQLL只需要INNDEX UUNIQUEE SCANN 操作。SELECCTLODGGINGFRROMLODDGINGWWHERELLODGINNG=ROOSEHILLL;索引范圍查查詢(xún)(INDDEX RAANGE SSCAN)適用于兩種種情況：1. 基于一個(gè)個(gè)范圍的檢索索2. 基于于非唯一性索索引的檢索例1：SELECCTLODGGINGFRROMLODDGINGWWHER

43、ELLODGINNGLIKEEM%;WHEREE子句條件包包括一系列值值， ORAACLE將通通過(guò)索引范圍圍查詢(xún)的方式式查詢(xún)LODDGING_PK . 由于索引范范圍查詢(xún)將返返回一組值， 它的效率就就要比索引唯唯一掃描低一一些。例2： SELEECTLODDGINGFFROMLOODGINGGWHEREEMANAGGER=BBILLGAATES;這個(gè)SQLL的執(zhí)行分兩兩步， LOODGINGG$MANAAGER的索索引范圍查詢(xún)?cè)?得到所有有符合條件記記錄的ROWWID)和下下一步同過(guò)RROWID訪訪問(wèn)表得到LLODGINNG列的值。 由于LODDGING$MANAGGER是一個(gè)個(gè)非唯一性的的索

44、引，數(shù)據(jù)據(jù)庫(kù)不能對(duì)它它執(zhí)行索引唯唯一掃描。由于SQLL返回LODDGING列列，而它并不不存在于LOODGINGG$MANAAGER索引引中， 所以以在索引范圍圍查詢(xún)后會(huì)執(zhí)執(zhí)行一個(gè)通過(guò)過(guò)ROWIDD訪問(wèn)表的操操作。WHEREE子句中， 如果索引列列所對(duì)應(yīng)的值值的第一個(gè)字字符由通配符符(WILDDCARD)開(kāi)始， 索索引將不被采采用。在這種種情況下，OORACLEE將使用全表表掃描。 SELEECTLODDGINGFFROMLOODGINGGWHEREEMANAGGERLIKKE%HAANMAN;27. 基基礎(chǔ)表的選擇擇基礎(chǔ)表(DDrivinng Tabble)是指指被最先訪問(wèn)問(wèn)的表(通常常以全

45、表掃描描的方式被訪訪問(wèn))。 根根據(jù)優(yōu)化器的的不同， SSQL語(yǔ)句中中基礎(chǔ)表的選選擇是不一樣樣的。如果你使用用的是CBOO (COSST BASSED OPPTIMIZZER)，優(yōu)優(yōu)化器會(huì)檢查查SQL語(yǔ)句句中的每個(gè)表表的物理大小小，索引的狀狀態(tài)，然后選選用花費(fèi)最低低的執(zhí)行路徑徑。如果你用RRBO (RRULE BBASED OPTIMMIZER) ， 并且且所有的連接接條件都有索索引對(duì)應(yīng)， 在這種情況況下， 基礎(chǔ)礎(chǔ)表就是FRROM 子句句中列在最后后的那個(gè)表。bblog舉例：SELECCTA.NAAME，B.MANAGGERFROOMWORRKERA，LODGINGBWHEREA.LODGING=B.LODING;由于LODGIING表的LLODINGG列上有一個(gè)個(gè)索引， 而而且WORKKER表中沒(méi)沒(méi)有相比較的的索引， WWORKERR表將被作為為查詢(xún)中的基基礎(chǔ)表。28. 多多個(gè)平等的索索引當(dāng)SQL語(yǔ)語(yǔ)句的執(zhí)行路路徑可以使用用分布在多個(gè)個(gè)表上的多