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

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

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

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

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

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

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

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

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

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

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

11、e)作作為基礎(chǔ)表， 交叉表是指指那個被其他他表所引用的的表。例如： 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_N

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

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

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

15、NO=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ù)庫訪訪問的檢索數(shù)數(shù)據(jù)量

16、，建建議值為2000.8. 使用用DECODDE函數(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)

17、D00020_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%;類似的，DDECODEE函數(shù)也可以以運(yùn)用于GRROUP BBY 和ORRDER BBY子句中。9. 整合合簡單，無關(guān)關(guān)聯(lián)的數(shù)據(jù)庫庫訪問如果你有幾幾個簡單的數(shù)數(shù)據(jù)庫查詢語語句，你可以以把它們整合合到一個查詢詢中(

18、即使它它們之間沒有有關(guān)系)例如：SELEECTNAMMEFROMMEMPWHHEREEMMP_NO=1234;SELECCTNAMEEFROMDDPTWHEEREDPTT_NO=110;SELLECTNAAMEFROOMCATWWHERECCAT_TYYPE=RRD;上面的3個個查詢可以被被合并成一個個： 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)刪除表中中的記錄時，在在通常情況下下， 回滾段段(rolllback segmeents

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

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

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

23、REGGION，AAVG(LOOG_SIZZE)FROOMLOCAATIONWWHERERREGIONNREGIOON!=SSYDNEYYANDRREGIONN!=PEERTHGGROUPBBYREGIION15. 減減少對表的查查詢在含有子查查詢的SQLL語句中，要要特別注意減減少對表的查查詢。例如：低效SELEECTTABB_NAMEEFROMTTABLESSWHEREETAB_NNAME=(SELECCTTAB_NAMEFFROMTAAB_COLLUMNSWHHEREVEERSIONN=604)ANDDDB_VERR=(SELLECTDBB_VERFFROMTAAB_COLLUMNSWW

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

25、MP_DEPT=0020;高效： UPDAATEEMPPSET(EEMP_CAAT,SALL_RANGGE)=(SSELECTTMAX(CCATEGOORY),MMAX(SAAL_RANNGE)FRROMEMPP_CATEEGORIEES)WHEEREEMPP_DEPTT=00200;16. 通通過內(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_T

26、YPE=T.HISST_TYPPEGROUUPBYH.EMPNOO,E.ENNAME,HH.HISTT_TYPEE,T.TYYPE_DEESC;通過調(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)常在論論壇中看到如如 能不能能用一個SQQL寫出。 的貼子子， 殊不知知復(fù)雜的SQQL往往犧牲牲了執(zhí)行效率率。 能夠掌掌握上面的運(yùn)運(yùn)用函數(shù)解決決問題的方法法在實(shí)際工作作中是非常有有意義的)低效：UPDAATEEMPPSETEMMP_CATT=(SELLECTMAAX(CATTEGORYY)FROMMEMP_CCATEGOORIES),SAL_RANGEE=(SELLECTMAAX(SALL_RANGGE)FROOMEMP_CATEGGORIESS)WHERR

29、EEMP_DEPT=0020;高效： UPDAATEEMPPSET(EEMP_CAAT,SALL_RANGGE)=(SSELECTTMAX(CCATEGOORY),MMAX(SAAL_RANNGE)FRROMEMPP_CATEEGORIEES)WHEEREEMPP_DEPTT=00200;16. 通通過內(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

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

31、VL(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_TY

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

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)提交一個個包含一對多多表信息(比比

34、如部門表和和雇員表)的的查詢時，避避免在SELLECT子句句中使用DIISTINCCT. 一般般可以考慮用用EXISTT替換例如：低效： SELEECTDISSTINCTTDEPT_NO,DEEPT_NAAMEFROOMDEPTTD,EMPPEWHERRED.DEEPT_NOO=E.DEEPT_NOO高效：SELECCTDEPTT_NO,DDEPT_NNAMEFRROMDEPPTDWHEEREEXIISTS(SSELECTTXFRROMEMPPEWHERREE.DEEPT_NOO=D.DEEPT_NOO);EXISTTS 使查詢詢更為迅速，因因?yàn)镽DBMMS核心模塊塊將在子查詢詢的條件一旦旦滿

35、足后，立立刻返回結(jié)果果。22. 識識別低效執(zhí)執(zhí)行的SQQL語句用下列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_RREA

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

37、X(UUNIQUEESCAN)OFPKK_DEPTT(UNIIQUE)SStatisstics-0reecursiivecallls2dbbblockkgets330conssistenntgetss0physsicalrreads00redossize25598byttessenntviaSSQL*Neettocllient5503byttesrecceiveddviaSQQL*Nettfromcclientt2SQL*Netrooundtrripstoo/frommcliennt0sorrts(meemory)0sortts(dissk)14rrowsprrocesssed通過以上分分析，

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. 用用索引提高效效率索引是表的的一個概念部部分，用來提提高檢索數(shù)據(jù)

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

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

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

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

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

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

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