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

1、oracle優(yōu)化1. 選用適合合的ORACCLE優(yōu)化器器 ORACCLE的優(yōu)化化器共有3種種：a. RULEE （基于規(guī)規(guī)則） b. COSTT （基于成成本） c. CHOOOSE （選選擇性） 設(shè)置缺缺省的優(yōu)化器器，可以通過(guò)過(guò)對(duì)initt.ora文文件中OPTTIMIZEER_MODDE參數(shù)的各各種聲明，如如RULE，CCOST，CCHOOSEE，ALL_ROWS，F(xiàn)FIRST_ROWS . 你當(dāng)然然也在SQLL句級(jí)或是會(huì)會(huì)話（sesssion）級(jí)級(jí)對(duì)其進(jìn)行覆覆蓋。 為了使使用基于成本本的優(yōu)化器（CCBO， CCost-BBased Optimmizer） ， 你必須須經(jīng)常運(yùn)行aanaly

2、zze 命令，以以增加數(shù)據(jù)庫(kù)庫(kù)中的對(duì)象統(tǒng)統(tǒng)計(jì)信息（oobjectt stattisticcs）的準(zhǔn)確確性。 如果數(shù)數(shù)據(jù)庫(kù)的優(yōu)化化器模式設(shè)置置為選擇性（CCHOOSEE），那么實(shí)實(shí)際的優(yōu)化器器模式將和是是否運(yùn)行過(guò)aanalyzze命令有關(guān)關(guān)。 如果ttable已已經(jīng)被anaalyze過(guò)過(guò)， 優(yōu)化器器模式將自動(dòng)動(dòng)成為CBOO ， 反之之，數(shù)據(jù)庫(kù)將將采用RULLE形式的優(yōu)優(yōu)化器。 在缺省省情況下，OORACLEE采用CHOOOSE優(yōu)化化器，為了避避免那些不必必要的全表掃掃描（fulll tabble sccan） ， 你必須盡量量避免使用CCHOOSEE優(yōu)化器，而而直接采用基基于規(guī)則或者者基于成本的

3、的優(yōu)化器。 2. 訪問(wèn)Taable的方方式ORACCLE 采用用兩種訪問(wèn)表表中記錄的方方式： a. 全表掃描描 全表掃描描就是順序地地訪問(wèn)表中每每條記錄。 ORACLLE采用一次次讀入多個(gè)數(shù)數(shù)據(jù)塊（daatabasse bloock）的方方式優(yōu)化全表表掃描。 b. 通過(guò)ROOWID訪問(wèn)問(wèn)表 你可以采采用基于ROOWID的訪訪問(wèn)方式情況況，提高訪問(wèn)問(wèn)表的效率， ROWIDD包含了表中中記錄的物理理位置信息ORACCLE采用索索引（INDDEX）實(shí)現(xiàn)現(xiàn)了數(shù)據(jù)和存存放數(shù)據(jù)的物物理位置（RROWID）之之間的聯(lián)系。 通常索引引提供了快速速訪問(wèn)ROWWID的方法法，因此那些些基于索引列列的查詢(xún)就可可以得

4、到性能能上的提高。 3. 共享SQQL語(yǔ)句 為了不不重復(fù)解析相相同的SQLL語(yǔ)句，在第第一次解析之之后， ORRACLE將將SQL語(yǔ)句句存放在內(nèi)存存中。這塊位位于系統(tǒng)全局局區(qū)域SGAA（systtem gllobal area）的的共享池（ssharedd bufffer poool）中的的內(nèi)存可以被被所有的數(shù)據(jù)據(jù)庫(kù)用戶共享享。 因此，當(dāng)當(dāng)你執(zhí)行一個(gè)個(gè)SQL語(yǔ)句句（有時(shí)被稱(chēng)稱(chēng)為一個(gè)游標(biāo)標(biāo)）時(shí)，如果果它和之前的的執(zhí)行過(guò)的語(yǔ)語(yǔ)句完全相同同， ORAACLE就能能很快獲得已已經(jīng)被解析的的語(yǔ)句以及最最好的執(zhí)行路路徑。 ORRACLE的的這個(gè)功能大大大地提高了了SQL的執(zhí)執(zhí)行性能并節(jié)節(jié)省了內(nèi)存的的使用。

5、 可惜的的是ORACCLE只對(duì)簡(jiǎn)簡(jiǎn)單的表提供供高速緩沖（ccache buffeering） ，這個(gè)功能能并不適用于于多表連接查查詢(xún)。 數(shù)據(jù)庫(kù)庫(kù)管理員必須須在initt.ora中中為這個(gè)區(qū)域域設(shè)置合適的的參數(shù)，當(dāng)這這個(gè)內(nèi)存區(qū)域域越大，就可可以保留更多多的語(yǔ)句，當(dāng)當(dāng)然被共享的的可能性也就就越大了。 當(dāng)你向向ORACLLE 提交一一個(gè)SQL語(yǔ)語(yǔ)句，ORAACLE會(huì)首首先在這塊內(nèi)內(nèi)存中查找相相同的語(yǔ)句。 這里需需要注明的是是，ORACCLE對(duì)兩者者采取的是一一種嚴(yán)格匹配配，要達(dá)成共共享，SQLL語(yǔ)句必須完完全相同（包包括空格，換換行等）。 共享的的語(yǔ)句必須滿滿足三個(gè)條件件： A. 字符級(jí)的比比較：

6、當(dāng)前被執(zhí)執(zhí)行的語(yǔ)句和和共享池中的的語(yǔ)句必須完完全相同。 例如： SELLECT * FROMM EMP； 和下列列每一個(gè)都不不同 SELLECT * fromm EMP； Sellect * Fromm Emp； SELLECT * FROM EMP； B. 兩個(gè)語(yǔ)句所所指的對(duì)象必必須完全相同同： 例如： 用戶 對(duì)對(duì)象名 如何何訪問(wèn) Jacck saal_limmit priivate synonnym Worrk_citty pubblic ssynonyym Plaant_deetail ppublicc synoonym Jill sall_limiit privvate ssynony

7、ym Worrk_citty pubblic ssynonyym Plaant_deetail ttable ownerr考慮一下下列列SQL語(yǔ)句句能否在這兩兩個(gè)用戶之間間共享。SQL 能否共共享 原因 select max(ssal_caap) frrom saal_limmit; 不不能 每個(gè)用用戶都有一個(gè)個(gè)privaate syynonymm - saal_limmit , 它們是不同同的對(duì)象 select countt(*0 ffrom wwork_ccity wwhere sdescc likee NEWW%; 能能 兩個(gè)用戶戶訪問(wèn)相同的的對(duì)象pubblic ssynonyym -

8、wwork_ccity select a.sdeesc,b.locattion ffrom wwork_ccity aa , pllant_ddetaill b whhere aa.cityy_id = b.ciity_idd 不能 用用戶jackk 通過(guò)prrivatee synoonym訪問(wèn)問(wèn)plantt_detaail 而jjill 是是表的所有者者,對(duì)象不同同. C. 兩個(gè)SQLL語(yǔ)句中必須須使用相同的的名字的綁定定變量（biind vaariablles） 例如：第一組的兩兩個(gè)SQL語(yǔ)語(yǔ)句是相同的的（可以共享享），而第二二組中的兩個(gè)個(gè)語(yǔ)句是不同同的（即使在在運(yùn)行時(shí)，賦賦于不同的綁綁定

9、變量相同同的值） a. selecct pinn ， naame frrom peeople wheree pin = ：bllk1.piin； selecct pinn ， naame frrom peeople wheree pin = ：bllk1.piin；b. selectt pin ， namme froom peoople wwhere pin = ：blkk1.ot_ind；selectt pin ， namme froom peoople wwhere pin = ：blkk1.ov_ind；4. 選擇最最有效率的表表名順序（只只在基于規(guī)則則的優(yōu)化器中中有效） ORAACLE

10、的解解析器按照從從右到左的順順序處理FRROM子句中中的表名，因因此FROMM子句中寫(xiě)在在最后的表（基基礎(chǔ)表 drrivingg tablle）將被最最先處理。 在FROMM子句中包含含多個(gè)表的情情況下，你必必須選擇記錄錄條數(shù)最少的的表作為基礎(chǔ)礎(chǔ)表。當(dāng)ORRACLE處處理多個(gè)表時(shí)時(shí)， 會(huì)運(yùn)用用排序及合并并的方式連接接它們。首先先，掃描第一一個(gè)表（FRROM子句中中最后的那個(gè)個(gè)表）并對(duì)記記錄進(jìn)行派序序，然后掃描描第二個(gè)表（FFROM子句句中最后第二二個(gè)表），最最后將所有從從第二個(gè)表中中檢索出的記記錄與第一個(gè)個(gè)表中合適記記錄進(jìn)行合并并。 例如： 表表 TAB11 16，3384 條記記錄 表表 T

11、AB22 1 條記記錄 選選擇TAB22作為基礎(chǔ)表表 （最好的的方法） sellect ccount（*） froom tabb1，tabb2 執(zhí)執(zhí)行時(shí)間0.96秒 選選擇TAB22作為基礎(chǔ)表表 （不佳的的方法） sellect ccount（*） froom tabb2，tabb1 執(zhí)執(zhí)行時(shí)間266.09秒 如如果有3個(gè)以以上的表連接接查詢(xún)， 那那就需要選擇擇交叉表（iinterssectioon tabble）作為為基礎(chǔ)表， 交叉表是指指那個(gè)被其他他表所引用的的表。 例如： EMMP表描述了了LOCATTION表和和CATEGGORY表的的交集。SELECT * FROM LOOCATIO

12、ON L , CCATEGOORY C, EEMP E WHERE EE.EMP_NO BEETWEENN 10000 AND 2000AND E.CCAT_NOO = C.CAT_NNOAND E.LLOCN = L.LOOCN 將比下列SQQL更有效率率SELECT * FROM EMMP E ,LOCATIOON L , CCATEGOORY CWHERE EE.CAT_NO = C.CATT_NOAND E.LLOCN = L.LOOCNAND E.EEMP_NOO BETWWEEN 11000 AAND 200005. WHEERE子句中中的連接順序序。 ORAACLE采用用自下而上

13、的的順序解析WWHERE子子句，根據(jù)這這個(gè)原理，表表之間的連接接必須寫(xiě)在其其他WHERRE條件之前前， 那些可可以過(guò)濾掉最最大數(shù)量記錄錄的條件必須須寫(xiě)在WHEERE子句的的末尾。 例如：（低效，執(zhí)行行時(shí)間1566.3秒）SELECT FROM EMMP EWHERE SSAL 500000AND JOB = MANNAGERAND 25 (SELEECT COOUNT(*) FROOM EMPP WHERRE MGRR=E.EMMPNO); (高效,執(zhí)行行時(shí)間10.6秒)SELECT FROM EMMP EWHERE 225 500000AND JOB = MANNAGER;6. SELEEC

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

15、如，以以下有三種方方法可以檢索索出雇員號(hào)等等于03422或02911的職員。 方法11 （最低效效） SELLECT EEMP_NAAME , SALARRY , GGRADE FROOM EMPP WHEERE EMMP_NO = 3422; SEELECT EMP_NNAME , SALAARY , GRADEE FROOM EMPP WHEERE EMMP_NO = 2911;方法2 （次低低效） DECLAARE CURSSOR C11 (E_NNO NUMMBER) IS SELEECT EMMP_NAMME,SALLARY,GGRADE FROMM EMP WHERRE EMPP_

16、NO = E_NOO; BEGGIN OPENN C1(3342); FETCCH C1 INTO ,.,. ; OOPEN CC1(2911); FETCHH C1 IINTO ,.,. ; CLOOSE C11; EEND;方法3 （高效效） SELLECT AA.EMP_NAME , A.SSALARYY , A.GRADEE, B.EMPP_NAMEE , B.SALARRY , BB.GRADDE FROOM EMPP A,EMMP B WHEERE A.EMP_NNO = 3342 ANDD B.EMP_NNO = 2291;注意：在SQL*PPlus ， SQL*FForms和和

17、Pro*CC中重新設(shè)置置ARRAYYSIZE參參數(shù)， 可以以增加每次數(shù)數(shù)據(jù)庫(kù)訪問(wèn)的的檢索數(shù)據(jù)量量 ，建議值值為200. 8. 使用用DECODDE函數(shù)來(lái)減減少處理時(shí)間間使用DECOODE函數(shù)可可以避免重復(fù)復(fù)掃描相同記記錄或重復(fù)連連接相同的表表。 例如： SELEECT COOUNT(*)，SUMM(SAL) FROMMEMP WHERRE DEPPT_NO = 00220 AND ENAMEE LIKEESMITHH%; SELEECT COOUNT(*)，SUMM(SAL) FROMMEMP WHERRE DEPPT_NO = 00330 AND ENAMEE LIKEESMITHH%;你可

18、以用DEECODE函函數(shù)高效地得得到相同結(jié)果果SELECT COUNTT(DECOODE(DEEPT_NOO,00200,X,NULL) D00020_COOUNT, COUNNT(DECCODE(DDEPT_NNO,00330,X,NULLL) D00030_CCOUNT, SUM(DECODDE(DEPPT_NO,0020,SAL,NNULL) D00220_SALL, SUM(DECODDE(DEPPT_NO,0030,SAL,NNULL) D00330_SALLFROM EMMP WHEERE ENNAME LLIKE SMITHH%;類(lèi)似的，DEECODE函函數(shù)也可以運(yùn)運(yùn)用于GROO

19、UP BYY 和ORDDER BYY子句中。 9. 整合簡(jiǎn)單單，無(wú)關(guān)聯(lián)的的數(shù)據(jù)庫(kù)訪問(wèn)問(wèn)如果你有幾個(gè)個(gè)簡(jiǎn)單的數(shù)據(jù)據(jù)庫(kù)查詢(xún)語(yǔ)句句，你可以把把它們整合到到一個(gè)查詢(xún)中中（即使它們們之間沒(méi)有關(guān)關(guān)系） 例如： SELECTT NAMEE FROM EMMP WHERE EEMP_NOO = 12234; SELECTT NAMEE FROM DPPTWHERE DDPT_NOO = 100 ;SELECT NAME FROM CAATWHERE CCAT_TYYPE = RD;上面的3個(gè)查查詢(xún)可以被合合并成一個(gè)：SELECT E.NAMME , DD.NAMEE , C.NAMEFROM CAAT C ,

20、 DPT D , EEMP E,DUAL XWHERE NNVL(XX,X.DDUMMY) = NVVL(X,E.ROOWID(+)AND NVLL(X,X.DUMMMY) = NVL(X,DD.ROWIID(+)AND NVLL(X,X.DUMMMY) = NVL(X,CC.ROWIID(+)AND E.EEMP_NOO(+) = 12344AND D.DDEPT_NNO(+) = 10AND C.CCAT_TYYPE(+) = RRD;（譯者按： 雖雖然采取這種種方法，效率率得到提高，但但是程序的可可讀性大大降降低，所以讀讀者還是要權(quán)權(quán)衡之間的利利弊）10. 刪除除重復(fù)記錄最高效的刪除除重

21、復(fù)記錄方方法 （ 因因?yàn)槭褂昧薘ROWID）DELETE FROM EMP EEWHERE EE.ROWIID (SELECCT MINN(X.ROOWID) FROOM EMPP X WHEERE X.EMP_NNO = EE.EMP_NO);11. 用用TRUNCCATE替代代DELETTE當(dāng)刪除表中的的記錄時(shí)，在在通常情況下下， 回滾段段（rolllback segmeents ） 用來(lái)存放可可以被恢復(fù)的的信息。 如如果你沒(méi)有CCOMMITT事務(wù)，ORRACLE會(huì)會(huì)將數(shù)據(jù)恢復(fù)復(fù)到刪除之前前的狀態(tài)（準(zhǔn)準(zhǔn)確地說(shuō)是恢恢復(fù)到執(zhí)行刪刪除命令之前前的狀況） 而當(dāng)運(yùn)運(yùn)用TRUNNCATE時(shí)時(shí)， 回滾段

22、段不再存放任任何可被恢復(fù)復(fù)的信息。當(dāng)當(dāng)命令運(yùn)行后后，數(shù)據(jù)不能能被恢復(fù)。因因此很少的資資源被調(diào)用，執(zhí)執(zhí)行時(shí)間也會(huì)會(huì)很短。 （譯者者按： TRRUNCATTE只在刪除除全表適用，TTRUNCAATE是DDDL不是DMML） 12. 盡量多多使用COMMMIT只要有可能，在在程序中盡量量多使用COOMMIT， 這樣程序的的性能得到提提高，需求也也會(huì)因?yàn)镃OOMMIT所所釋放的資源源而減少：CCOMMITT所釋放的資資源：a. 回滾滾段上用于恢恢復(fù)數(shù)據(jù)的信信息。 b. 被程序語(yǔ)語(yǔ)句獲得的鎖鎖c. reedo loog bufffer 中中的空間d. ORRACLE為為管理上述33種資源中的的內(nèi)部花費(fèi)（

23、譯者按： 在使用COOMMIT時(shí)時(shí)必須要注意意到事務(wù)的完完整性，現(xiàn)實(shí)實(shí)中效率和事事務(wù)完整性往往往是魚(yú)和熊熊掌不可得兼兼） 13. 計(jì)算記記錄條數(shù)和一般的觀點(diǎn)點(diǎn)相反， ccount（*） 比coount（11）稍快 ， 當(dāng)然如果可可以通過(guò)索引引檢索，對(duì)索索引列的計(jì)數(shù)數(shù)仍舊是最快快的。 例如如 COUNNT（EMPPNO） （譯者者按： 在CCSDN論壇壇中，曾經(jīng)對(duì)對(duì)此有過(guò)相當(dāng)當(dāng)熱烈的討論論， 作者的的觀點(diǎn)并不十十分準(zhǔn)確，通通過(guò)實(shí)際的測(cè)測(cè)試，上述三三種方法并沒(méi)沒(méi)有顯著的性性能差別） 14. 用Whhere子句句替換HAVVING子句句避免使用HAAVING子子句， HAAVING 只會(huì)在檢索索出所

24、有記錄錄之后才對(duì)結(jié)結(jié)果集進(jìn)行過(guò)過(guò)濾。 這個(gè)個(gè)處理需要排排序，總計(jì)等等操作。 如如果能通過(guò)WWHERE子子句限制記錄錄的數(shù)目，那那就能減少這這方面的開(kāi)銷(xiāo)銷(xiāo)。 例如：低效： SEELECT REGIOON，AVGG(LOG_SIZE) FRROM LOOCATIOON GRROUP BBY REGGION HAAVING REGIOON REGGION != SYYDNEY ANND REGGION != PEERTH高效 SEELECT REGIOON，AVGG(LOG_SIZE) FRROM LOOCATIOON WHHERE RREGIONN REGIION != SYDDNEY ANND

25、REGGION != PEERTHGROUP BBY REGGION（譯者按： HHAVINGG 中的條件件一般用于對(duì)對(duì)一些集合函函數(shù)的比較，如如COUNTT（） 等等等。 除此而而外，一般的的條件應(yīng)該寫(xiě)寫(xiě)在WHERRE子句中） 15. 減少對(duì)對(duì)表的查詢(xún)?cè)诤凶硬樵?xún)?cè)兊腟QL語(yǔ)語(yǔ)句中，要特特別注意減少少對(duì)表的查詢(xún)?cè)儭?例如：低效 SEELECT TAB_NNAME FRROM TAABLES WHHERE TTAB_NAAME = ( SELLECT TTAB_NAAME FROMM TAB_COLUMMNS WHEREE VERSSION = 604) ANNDDB_VER= ( SELLE

26、CT DDB_VERR FROM TAB_CCOLUMNNS WHEREE VERSSION = 604) 高效 SEELECT TAB_NNAME FRROM TAABLES WHHERE (TAB_NNAME,DDB_VERR) = ( SEELECT TAB_NNAME,DDB_VERR) FROOM TABB_COLUUMNS WHEERE VEERSIONN = 6004) Upddate 多多個(gè)Coluumn 例子子：低效： UUPDATEE EMP SSET EMMP_CATT = (SSELECTT MAX(CATEGGORY) FROM EMP_CCATEGOORIES),

27、SALL_RANGGE = (SELECCT MAXX(SAL_RANGEE) FROOM EMPP_CATEEGORIEES) WWHERE EMP_DDEPT = 00200;高效： UPPDATE EMP SEET (EMMP_CATT, SALL_RANGGE) = (SELEECT MAAX(CATTEGORYY) , MMAX(SAAL_RANNGE) FROM EEMP_CAATEGORRIES) WWHERE EMP_DDEPT = 00200;16. 通過(guò)過(guò)內(nèi)部函數(shù)提提高SQL效效率。 SSELECTT H.EMMPNO,EE.ENAMME,H.HHIST_TTYPE,TT.

28、TYPEE_DESCC,COUNNT(*) FRROM HIISTORYY_TYPEE T,EMMP E,EEMP_HIISTORYY H WHHERE HH.EMPNNO = EE.EMPNNOAND H.HHIST_TTYPE = T.HIIST_TYYPEGROUP BBY H.EEMPNO,E.ENAAME,H.HIST_TYPE,T.TYPPE_DESSC;通過(guò)調(diào)用下面面的函數(shù)可以以提高效率。FUNCTIOON LOOOKUP_HHIST_TTYPE(TTYP INN NUMBBER) RRETURNN VARCCHAR2AS TDEESC VAARCHARR2(30); CURRS

29、OR CC1 IS SELEECT TYYPE_DEESC FROMM HISTTORY_TTYPE WHERRE HISST_TYPPE = TTYP;BEGIN OPEN C1; FETTCH C11 INTOO TDESSC; CLOOSE C11; RETTURN (NVL(TTDESC,?);END;FUNCTIOON LOOOKUP_EEMP(EMMP IN NUMBEER) REETURN VARCHHAR2AS ENAAME VAARCHARR2(30); CURRSOR CC1 IS SELEECT ENNAME FROMM EMP WHERRE EMPPNO=EMMP;BE

30、GIN OPEEN C1; FETTCH C11 INTOO ENAMME; CLOOSE C11; RETTURN (NVL(EENAME,?);END;SELECT H.EMPPNO,LOOOKUP_EMP(HH.EMPNNO),H.HIST_TYPE,LOOKUUP_HISST_TYPPE(H.HHIST_TTYPE),COUNTT(*)FROM EMMP_HISSTORY HGROUP BBY H.EEMPNO , H.HHIST_TTYPE;（譯者按： 經(jīng)經(jīng)常在論壇中中看到如 能不能用一一個(gè)SQL寫(xiě)寫(xiě)出。 的貼子， 殊不知復(fù)雜雜的SQL往往往犧牲了執(zhí)執(zhí)行效率。 能夠掌握上上面的運(yùn)用函

31、函數(shù)解決問(wèn)題題的方法在實(shí)實(shí)際工作中是是非常有意義義的）17. 使用用表的別名（AAlias） 當(dāng)在SSQL語(yǔ)句中中連接多個(gè)表表時(shí)， 請(qǐng)使使用表的別名名并把別名前前綴于每個(gè)CColumnn上。這樣一一來(lái)，就可以以減少解析的的時(shí)間并減少少那些由Coolumn歧歧義引起的語(yǔ)語(yǔ)法錯(cuò)誤。 （譯者者注： Coolumn歧歧義指的是由由于SQL中中不同的表具具有相同的CColumnn名，當(dāng)SQQL語(yǔ)句中出出現(xiàn)這個(gè)Coolumn時(shí)時(shí)，SQL解解析器無(wú)法判判斷這個(gè)Coolumn的的歸屬） 18. 用EXXISTS替替代IN在許多基于基基礎(chǔ)表的查詢(xún)?cè)冎校瑸榱藵M滿足一個(gè)條件件，往往需要要對(duì)另一個(gè)表表進(jìn)行聯(lián)接。在這種

32、情況況下， 使用用EXISTTS（或NOOT EXIISTS）通通常將提高查查詢(xún)的效率。 低效：SELECT * FROM EMMP (基礎(chǔ)礎(chǔ)表)WHERE EEMPNO 0AND DEPPTNO IIN (SEELECT DEPTNNO FROM DEEPT WHERE LLOC = MELBB) 高效：SELECT * FROM EMMP (基礎(chǔ)礎(chǔ)表)WHERE EEMPNO 0AND EXIISTS (SELECCT X FROM DEEPT WHERE DDEPT.DDEPTNOO = EMMP.DEPPTNOAND LOCC = MMELB) （譯者者按： 相對(duì)對(duì)來(lái)說(shuō)，用NNOT E

33、XXISTS替替換NOT IN 將更更顯著地提高高效率，下一一節(jié)中將指出出） 19. 用NOOT EXIISTS替代代NOT IIN在子查詢(xún)中，NNOT INN子句將執(zhí)行行一個(gè)內(nèi)部的的排序和合并并。 無(wú)論在在哪種情況下下，NOT IN都是最最低效的 （因因?yàn)樗鼘?duì)子查查詢(xún)中的表執(zhí)執(zhí)行了一個(gè)全全表遍歷）。 為了避避免使用NOOT IN ，我們可以以把它改寫(xiě)成成外連接（OOuter Joinss）或NOTT EXISSTS.例如：SELECT FROM EMMPWHERE DDEPT_NNO NOTT IN (SELECCT DEPPT_NO FRROM DEEPT WHHERE DDEPT_CCA

34、T=AA);為了提高效率率。改寫(xiě)為：（方法一： 高效）SELECT .FROM EMMP A,DDEPT BBWHERE AA.DEPTT_NO = B.DEEPT(+)AND B.DDEPT_NNO IS NULLAND B.DDEPT_CCAT(+) = AA（方法二： 最高效）SELECT .FROM EMMP EWHERE NNOT EXXISTS (SELEECT XX FRROM DEEPT D WHHERE DD.DEPTT_NO = E.DEEPT_NOO ANND DEPPT_CATT = AA);20. 用用表連接替換換EXISTTS通常來(lái)說(shuō) ， 采用表連接接的方式比EEX

35、ISTSS更有效率 SSELECTT ENAMME FFROM EEMP E WWHERE EXISTTS (SEELECT X FROM DEPT WHEREE DEPTT_NO = E.DEEPT_NOO ANDD DEPTT_CAT = A);（更高效） SSELECTT ENAMME FFROM DDEPT DD,EMP E WWHERE E.DEPPT_NO = D.DDEPT_NNO AAND DEEPT_CAAT = A ;（譯者按： 在RBO的的情況下，前前者的執(zhí)行路路徑包括FIILTER，后后者使用NEESTED LOOP） 21. 用EXXISTS替替換DISTTINCT當(dāng)

36、提交一個(gè)包包含一對(duì)多表表信息（比如如部門(mén)表和雇雇員表）的查查詢(xún)時(shí)，避免免在SELEECT子句中中使用DISSTINCTT. 一般可可以考慮用EEXIST替替換例如：低效： SELEECT DIISTINCCT DEPPT_NO,DEPT_NAME FROOM DEPPT D,EEMP E WHEERE D.DEPT_NO = E.DEPPT_NO高效： SELLECT DDEPT_NNO,DEPPT_NAMME FROOM DEPPT D WHEERE EXXISTS ( SELLECT X FRROM EMMP E WHERRE E.DDEPT_NNO = DD.DEPTT_NO);EXIS

37、TSS 使查詢(xún)更更為迅速，因因?yàn)镽DBMMS核心模塊塊將在子查詢(xún)?cè)兊臈l件一旦旦滿足后，立立刻返回結(jié)果果。 22. 識(shí)別低效執(zhí)行的SQL語(yǔ)語(yǔ)句用下列SQLL工具找出低低效SQL：SELECT EXECUUTIONSS , DIISK_REEADS, BUFFEER_GETTS, ROUNND(BUUFFER_GETS-DISK_READSS)/BUFFFER_GGETS,22) Hitt_radiio, ROOUND(DDISK_RREADS/EXECUUTIONSS,2) RReads_per_rrun, SQL_TEXTFROM V$SQLLAREAWHERE EEXECUTTIONS0AN

38、D BUFFFER_GEETS 0 AND (BUUFFER_GETS-DISK_READSS)/BUFFFER_GGETS llist 1 SELEECT * 2 FROMM deptt, empp 3* WHHERE eemp.deeptno = deppt.depptnoSQL seet auttotracce traaceonlly /*ttraceoonly 可可以不顯示執(zhí)執(zhí)行結(jié)果*/SQL /14 rowss seleected.Executiion Pllan- 0 SEELECT STATEEMENT Optimmizer=CHOOSSE 1 0 NESTEED LOOOPS

39、2 1 TABBLE ACCCESS (FULLL) OF EMP 3 1 TABBLE ACCCESS (BY IINDEX ROWIDD) OF DEPTT 4 3 IINDEX (UNIQQUE SCCAN) OOF PKK_DEPTT (UNNIQUE)Statisttics- 0 recurrsive callss 2 db bllock ggets 30 consiistentt getss 0 physiical rreads 0 redo size 2598 bytess sentt via SQL*NNet too clieent 503 bytess receeived v

40、ia SSQL*Neet froom cliient 2 SQL*NNet rooundtrrips tto/froom cliient 0 sortss (memmory) 0 sortss (dissk) 14 rows proceessed通過(guò)以上分析析，可以得出出實(shí)際的執(zhí)行行步驟是：1. TAABLE AACCESSS （FULLL） OFF EMPP2. INNDEX （UUNIQUEE SCANN） OF PK_DDEPT （UNIQQUE） 3. TABLLE ACCCESS （BBY INDDEX ROOWID） OF DDEPT 4. NEESTED LOOPSS （JOII

41、NING 1 ANDD 3） 注： 目前許多第第三方的工具具如TOADD和ORACCLE本身提提供的工具如如OMS的SSQL Annalyzee都提供了極極其方便的EEXPLAIIN PLAAN工具。也也許喜歡圖形形化界面的朋朋友們可以選選用它們。 25. 用索引引提高效率 索引是是表的一個(gè)概概念部分，用用來(lái)提高檢索索數(shù)據(jù)的效率率。 實(shí)際上上，ORACCLE使用了了一個(gè)復(fù)雜的的自平衡B-tree結(jié)結(jié)構(gòu)。 通常常，通過(guò)索引引查詢(xún)數(shù)據(jù)比比全表掃描要要快。 當(dāng)OORACLEE找出執(zhí)行查查詢(xún)和Upddate語(yǔ)句句的最佳路徑徑時(shí)， ORRACLE優(yōu)優(yōu)化器將使用用索引。 同同樣在聯(lián)結(jié)多多個(gè)表時(shí)使用用索引也

42、可以以提高效率。 另一個(gè)使使用索引的好好處是，它提提供了主鍵（pprimarry keyy）的唯一性性驗(yàn)證。 除了那那些LONGG或LONGG RAW數(shù)數(shù)據(jù)類(lèi)型， 你可以索引引幾乎所有的的列。 通常常， 在大型型表中使用索索引特別有效效。 當(dāng)然，你你也會(huì)發(fā)現(xiàn)， 在掃描小表表時(shí)，使用索索引同樣能提提高效率。 雖然使使用索引能得得到查詢(xún)效率率的提高，但但是我們也必必須注意到它它的代價(jià)。 索引需要空空間來(lái)存儲(chǔ)，也也需要定期維維護(hù)， 每當(dāng)當(dāng)有記錄在表表中增減或索索引列被修改改時(shí)， 索引引本身也會(huì)被被修改。 這這意味著每條條記錄的INNSERT ， DELLETE ， UPDATTE將為此多多付出4 ，

43、 5 次的磁磁盤(pán)I/O . 因?yàn)樗魉饕枰~外外的存儲(chǔ)空間間和處理，那那些不必要的的索引反而會(huì)會(huì)使查詢(xún)反應(yīng)應(yīng)時(shí)間變慢。 譯者按按：定期的重重構(gòu)索引是有有必要的。 ALTTER INNDEX REBUUILD 26. 索引的的操作 ORAACLE對(duì)索索引有兩種訪訪問(wèn)模式。 索引唯唯一掃描 （ INDEXX UNIQQUE SCCAN） 大多數(shù)數(shù)情況下， 優(yōu)化器通過(guò)過(guò)WHEREE子句訪問(wèn)IINDEX. 例如：表LODGIING有兩個(gè)個(gè)索引 ： 建立在LOODGINGG列上的唯一一性索引LOODGINGG_PK和建建立在MANNAGER列列上的非唯一一性索引LOODGINGG$MANAAGER. S

44、ELLECT * FROM LLODGINNG WHERE LODGIING = ROSE HILL； 在內(nèi)部部 ， 上述述SQL將被被分成兩步執(zhí)執(zhí)行， 首先先 ， LOODGINGG_PK 索索引將通過(guò)索索引唯一掃描描的方式被訪訪問(wèn) ， 獲獲得相對(duì)應(yīng)的的ROWIDD， 通過(guò)RROWID訪訪問(wèn)表的方式式執(zhí)行下一步步檢索。 如果被被檢索返回的的列包括在IINDEX列列中，ORAACLE將不不執(zhí)行第二步步的處理（通通過(guò)ROWIID訪問(wèn)表）。 因?yàn)闄z索索數(shù)據(jù)保存在在索引中， 單單訪問(wèn)索索引就可以完完全滿足查詢(xún)?cè)兘Y(jié)果。 下面SSQL只需要要INDEXX UNIQQUE SCCAN 操作作。 SELLE

45、CT LLODGINNG FROM LLODGINNG WHERE LODGIING = ROSE HILL； 索引范范圍查詢(xún)（IINDEX RANGEE SCANN） 適用于于兩種情況：1. 基于于一個(gè)范圍的的檢索2. 基于于非唯一性索索引的檢索 例1： SELLECT LLODGINNG FROOM LODDGING WHEREE LODGGING LLIKE M%； WHEERE子句條條件包括一系系列值， OORACLEE將通過(guò)索引引范圍查詢(xún)的的方式查詢(xún)LLODGINNG_PK . 由于索索引范圍查詢(xún)?cè)儗⒎祷匾唤M組值， 它的的效率就要比比索引唯一掃掃描低一些。 例2： SELLECT L

46、LODGINNG FROM LLODGINNG WHERE MANAGGER = BILL GATESS； 這個(gè)SSQL的執(zhí)行行分兩步， LODGIING$MAANAGERR的索引范圍圍查詢(xún)（得到到所有符合條條件記錄的RROWID） 和下一步同同過(guò)ROWIID訪問(wèn)表得得到LODGGING列的的值。 由于于LODGIING$MAANAGERR是一個(gè)非唯唯一性的索引引，數(shù)據(jù)庫(kù)不不能對(duì)它執(zhí)行行索引唯一掃掃描。 由于SSQL返回LLODGINNG列，而它它并不存在于于LODGIING$MAANAGERR索引中， 所以在索引引范圍查詢(xún)后后會(huì)執(zhí)行一個(gè)個(gè)通過(guò)ROWWID訪問(wèn)表表的操作。 WHEERE子句中中

47、， 如果索索引列所對(duì)應(yīng)應(yīng)的值的第一一個(gè)字符由通通配符（WIILDCARRD）開(kāi)始， 索引將不被被采用。在這這種情況下，OORACLEE將使用全表表掃描。SELECT LODGIING FROM LLODGINNG WHERE MANAGGER LIIKE HANMMAN；27. 基礎(chǔ)礎(chǔ)表的選擇 基礎(chǔ)表表（Drivving TTable）是是指被最先訪訪問(wèn)的表（通通常以全表掃掃描的方式被被訪問(wèn)）。 根據(jù)優(yōu)化器器的不同， SQL語(yǔ)句句中基礎(chǔ)表的的選擇是不一一樣的。 如果你你使用的是CCBO （CCOST BBASED OPTIMMIZER），優(yōu)優(yōu)化器會(huì)檢查查SQL語(yǔ)句句中的每個(gè)表表的物理大小小，索

48、引的狀狀態(tài)，然后選選用花費(fèi)最低低的執(zhí)行路徑徑。 如果你你用RBO （RULEE BASEED OPTTIMIZEER） ， 并且所有的的連接條件都都有索引對(duì)應(yīng)應(yīng)， 在這種種情況下， 基礎(chǔ)表就是是FROM 子句中列在在最后的那個(gè)個(gè)表。 舉例：SELECTT A.NAAME ， B.MANNAGER FROMWWORKERR A，LOODGINGG B WHEREA.LODDGING = B.LLODINGG；由于LODGGING表的的LODINNG列上有一一個(gè)索引， 而且WORRKER表中中沒(méi)有相比較較的索引， WORKEER表將被作作為查詢(xún)中的的基礎(chǔ)表。 28. 多個(gè)平平等的索引當(dāng)SQL語(yǔ)句句

49、的執(zhí)行路徑徑可以使用分分布在多個(gè)表表上的多個(gè)索索引時(shí)， OORACLEE會(huì)同時(shí)使用用多個(gè)索引并并在運(yùn)行時(shí)對(duì)對(duì)它們的記錄錄進(jìn)行合并， 檢索出僅對(duì)對(duì)全部索引有有效的記錄。 在ORRACLE選選擇執(zhí)行路徑徑時(shí)，唯一性性索引的等級(jí)級(jí)高于非唯一一性索引。 然而這個(gè)規(guī)規(guī)則只有當(dāng)WWHERE子子句中索引列列和常量比較較才有效。如如果索引列和和其他表的索索引類(lèi)相比較較。 這種子子句在優(yōu)化器器中的等級(jí)是是非常低的。 如果不不同表中兩個(gè)個(gè)想同等級(jí)的的索引將被引引用， FRROM子句中中表的順序?qū)Q定哪個(gè)會(huì)會(huì)被率先使用用。 FROOM子句中最最后的表的索索引將有最高高的優(yōu)先級(jí)。 如果相相同表中兩個(gè)個(gè)想同等級(jí)的的索

50、引將被引引用， WHHERE子句句中最先被引引用的索引將將有最高的優(yōu)優(yōu)先級(jí)。 舉例：DEPTNOO上有一個(gè)非非唯一性索引引，EMP_CAT也有有一個(gè)非唯一一性索引。 SELLECT EENAME，F(xiàn)ROM EEMP WHERE DEPT_NO = 20 AND EMMP_CATT = A；這里，DEPPTNO索引引將被最先檢檢索，然后同同EMP_CCAT索引檢檢索出的記錄錄進(jìn)行合并。 執(zhí)行路徑徑如下： TABBLE ACCCESS BY ROOWID OON EMPP AND-EEQUAL INDEEX RANNGE SCCAN ONN DEPTT_IDX INDEEX RANNGE SCCA

51、N ONN CAT_IDX 29. 等式式比較和范圍圍比較當(dāng)WHEREE子句中有索索引列， OORACLEE不能合并它它們，ORAACLE將用用范圍比較。 舉例：DEPTNOO上有一個(gè)非非唯一性索引引，EMP_CAT也有有一個(gè)非唯一一性索引。 SELLECT EENAME FROM EEMP WHERE DEPTNNO 220 AND EMMP_CATT = A； 這里只只有EMP_CAT索引引被用到，然然后所有的記記錄將逐條與與DEPTNNO條件進(jìn)行行比較。 執(zhí)執(zhí)行路徑如下下：TABLE ACCESSS BY ROWIDD ON EEMP INDEXX RANGGE SCAAN ON CAT

52、_IIDX 30. 不明確確的索引等級(jí)級(jí) 當(dāng)ORRACLE無(wú)無(wú)法判斷索引引的等級(jí)高低低差別，優(yōu)化化器將只使用用一個(gè)索引，它它就是在WHHERE子句句中被列在最最前面的。 舉例：DEPTNOO上有一個(gè)非非唯一性索引引，EMP_CAT也有有一個(gè)非唯一一性索引。 SELLECT EENAME FROM EEMP WHERE DEPTNNO 220 AND EMMP_CATT A； 這里， ORACLLE只用到了了DEPT_NO索引。 執(zhí)行路徑徑如下： TABBLE ACCCESS BY ROOWID OON EMPP INDEXX RANGGE SCAAN ON DEPT_IDX 譯者按按：我們來(lái)試

53、試一下以下這這種情況：SQL sselectt indeex_namme， unniquenness ffrom uuser_iindexees wheere taable_nname = EMPP； INDDEX_NAAME UNNIQUENNES- - EMPPNO UNNIQUE EMPTYPPE NONUNNIQUE SQLL sellect * fromm emp wheree empnno = 2 andd emp_type = A ； no rows seleccted Exeecutioon Plaan- 0 SSELECTT STATTEMENTT Optiimizerr=C

54、HOOOSE 1 00 TAABLE AACCESSS （BY INDEXX ROWIID） OFF EMPP 2 11 INDEXX （RANNGE SCCAN） OOF EMMPTYPEE （NOON-UNIIQUE）雖然EMPNOO是唯一性索索引，但是由由于它所做的的是范圍比較較， 等級(jí)要要比非唯一性性索引的等式式比較低！31. 強(qiáng)制制索引失效 如果兩兩個(gè)或以上索索引具有相同同的等級(jí)，你你可以強(qiáng)制命命令ORACCLE優(yōu)化器器使用其中的的一個(gè)（通過(guò)過(guò)它，檢索出出的記錄數(shù)量量少） . 舉例： SELLECT EENAME FROM EEMP WHERE EMPNOO = 79935 AND

55、DEEPTNO + 0 = 10 /*DDEPTNOO上的索引將將失效*/ AND EMMP_TYPPE | = A /*EEMP_TYYPE上的索索引將失效*/ 這是一一種相當(dāng)直接接的提高查詢(xún)?cè)冃实霓k法法。 但是你你必須謹(jǐn)慎考考慮這種策略略，一般來(lái)說(shuō)說(shuō)，只有在你你希望單獨(dú)優(yōu)優(yōu)化幾個(gè)SQQL時(shí)才能采采用它。 這里有有一個(gè)例子關(guān)關(guān)于何時(shí)采用用這種策略， 假設(shè)在在EMP表的的EMP_TTYPE列上上有一個(gè)非唯唯一性的索引引而EMP_CLASSS上沒(méi)有索引引。 SELLECT EENAME FROM EEMP WHERE EMP_TTYPE = AAND EMMP_CLAASS = X； 優(yōu)化器器

56、會(huì)注意到EEMP_TYYPE上的索索引并使用它它。 這是目目前唯一的選選擇。 如果果，一段時(shí)間間以后， 另另一個(gè)非唯一一性建立在EEMP_CLLASS上，優(yōu)優(yōu)化器必須對(duì)對(duì)兩個(gè)索引進(jìn)進(jìn)行選擇，在在通常情況下下，優(yōu)化器將將使用兩個(gè)索索引并在他們們的結(jié)果集合合上執(zhí)行排序序及合并。 然而，如果果其中一個(gè)索索引（EMPP_TYPEE）接近于唯唯一性而另一一個(gè)索引（EEMP_CLLASS）上上有幾千個(gè)重重復(fù)的值。 排序及合并并就會(huì)成為一一種不必要的的負(fù)擔(dān)。 在在這種情況下下，你希望使使優(yōu)化器屏蔽蔽掉EMP_CLASSS索引。 用下面面的方案就可可以解決問(wèn)題題。 SELLECT EENAME FROM EE

57、MP WHERE EMP_TTYPE = AAND EMMP_CLAASS| = X； 32. 避免免在索引列上上使用計(jì)算。 WHEERE子句中中，如果索引引列是函數(shù)的的一部分。優(yōu)優(yōu)化器將不使使用索引而使使用全表掃描描。 舉例： 低效： SELECTT FROOM DEPPT WHERE SSAL * 12 250000； 高效： SELECTT FROOM DEPPT WHERE SAL 250000/12； 譯者按按：這是一個(gè)個(gè)非常實(shí)用的的規(guī)則，請(qǐng)務(wù)務(wù)必牢記 33. 自動(dòng)選選擇索引 如果表中有兩兩個(gè)以上（包包括兩個(gè)）索索引，其中有有一個(gè)唯一性性索引，而其其他是非唯一一性。 在這種種情況下，

58、OORACLEE將使用唯一一性索引而完完全忽略非唯唯一性索引。 舉例： SELECTT ENAMME FROM EEMP WHERE EMPNOO = 23326 AND DEEPTNO = 20 ； 這里，只只有EMPNNO上的索引引是唯一性的的，所以EMMPNO索引引將用來(lái)檢索索記錄。 TABBLE ACCCESS BY ROOWID OON EMPP INDEXX UNIQQUE SCCAN ONN EMP_NO_IDDX 34. 避免在在索引列上使使用NOT 通常，我們要要避免在索引引列上使用NNOT， NNOT會(huì)產(chǎn)生生在和在索引引列上使用函函數(shù)相同的影影響。 當(dāng)OORACLEE“遇到

59、”NOT，他他就會(huì)停止使使用索引轉(zhuǎn)而而執(zhí)行全表掃掃描。 舉例： 低效： （這里，不不使用索引） SELLECT FROOM DEPPT WHERE DEPT_CODE NOT = 0； 高效： （這里，使使用了索引） SELLECT FROOM DEPPT WHERE DEPT_CODE 0； 需要注注意的是，在在某些時(shí)候， ORACLLE優(yōu)化器會(huì)會(huì)自動(dòng)將NOOT轉(zhuǎn)化成相相對(duì)應(yīng)的關(guān)系系操作符。 NOTT too = to NOT = NOT 譯者按按：在這個(gè)例例子中，作者者犯了一些錯(cuò)錯(cuò)誤。 例子子中的低效率率SQL是不不能被執(zhí)行的的。 我做了了一些測(cè)試： SQLL sellect * from

60、m emp wheree NOT empnoo 1； no rowws sellectedd Executtion PPlan - 0 SSELECTT STATTEMENTT Optiimizerr=CHOOOSE 1 00 TAABLE AACCESSS （BY INDEXX ROWIID） OFF EMPP 2 11 INDEXX （RANNGE SCCAN） OOF EMMPNO （UNIQQUE） SQLL sellect * fromm emp wheree empnno =替代 如果DDEPTNOO上有一個(gè)索索引， 高效： SELLECT * FROM EEMP WHERE DE