優(yōu)化oracle中的sql語句

1、優(yōu)化oracle中的sql語句 （1） 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)：ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，F(xiàn)ROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理，在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。如果有3個(gè)以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個(gè)被其他表所引用的表. （2） WHEREE子句中的連連接順序：ORACLLE采用自下下而上的順序序解析WHEERE子句,根據(jù)這個(gè)原原理,表之間間的連接必須須寫在其他WWHER

2、E條條件之前, 那些可以過過濾掉最大數(shù)數(shù)量記錄的條條件必須寫在在WHEREE子句的末尾尾.（3） SSELECTT子句中避免免使用 * ：ORACLLE在解析的的過程中, 會(huì)將* 依次轉(zhuǎn)換換成所有的列列名, 這個(gè)個(gè)工作是通過過查詢數(shù)據(jù)字字典完成的, 這意味著著將耗費(fèi)更多多的時(shí)間（4） 減減少訪問數(shù)據(jù)據(jù)庫的次數(shù)：ORACLLE在內(nèi)部執(zhí)執(zhí)行了許多工工作: 解析析SQL語句句, 估算索索引的利用率率, 綁定變變量 , 讀讀數(shù)據(jù)塊等；（5） 在在SQL*PPlus , SQL*Formss和Pro*C中重新設(shè)設(shè)置ARRAAYSIZEE參數(shù), 可可以增加每次次數(shù)據(jù)庫訪問問的檢索數(shù)據(jù)據(jù)量 ,建議議值為20

3、00（6） 使使用DECOODE函數(shù)來來減少處理時(shí)時(shí)間：使用DECCODE函數(shù)數(shù)可以避免重重復(fù)掃描相同同記錄或重復(fù)復(fù)連接相同的的表.（7） 整整合簡(jiǎn)單,無無關(guān)聯(lián)的數(shù)據(jù)據(jù)庫訪問：如果你有幾幾個(gè)簡(jiǎn)單的數(shù)數(shù)據(jù)庫查詢語語句,你可以以把它們整合合到一個(gè)查詢?cè)冎?即使它它們之間沒有有關(guān)系)（8） 刪刪除重復(fù)記錄錄：最高效的刪刪除重復(fù)記錄錄方法 ( 因?yàn)槭褂昧肆薘OWIDD)例子：DELETTE FROOM EMPP E WHHERE EE.ROWIID (SELECCT MINN(X.ROOWID) FROM EMP XX WHERRE X.EEMP_NOO = E.EMP_NNO);（9） 用用TRUN

4、CCATE替代代DELETTE：當(dāng)刪除表中中的記錄時(shí),在通常情況況下, 回滾滾段(rolllbackk segmments ) 用來存存放可以被恢恢復(fù)的信息. 如果你沒沒有COMMMIT事務(wù),ORACLLE會(huì)將數(shù)據(jù)據(jù)恢復(fù)到刪除除之前的狀態(tài)態(tài)(準(zhǔn)確地說說是恢復(fù)到執(zhí)執(zhí)行刪除命令令之前的狀況況) 而當(dāng)運(yùn)運(yùn)用TRUNNCATE時(shí)時(shí), 回滾段段不再存放任任何可被恢復(fù)復(fù)的信息.當(dāng)當(dāng)命令運(yùn)行后后,數(shù)據(jù)不能能被恢復(fù).因因此很少的資資源被調(diào)用,執(zhí)行時(shí)間也也會(huì)很短. (譯者按: TRUNNCATE只只在刪除全表表適用,TRRUNCATTE是DDLL不是DMLL)（10） 盡量多使用用COMMIIT：只要有可能能,

5、在程序中中盡量多使用用COMMIIT, 這樣樣程序的性能能得到提高,需求也會(huì)因因?yàn)镃OMMMIT所釋放放的資源而減減少: COMMIIT所釋放的的資源: a. 回滾滾段上用于恢恢復(fù)數(shù)據(jù)的信信息. b. 被程程序語句獲得得的鎖 c. reedo loog bufffer 中中的空間 d. ORRACLE為為管理上述33種資源中的的內(nèi)部花費(fèi)（11） 用Wherre子句替換換HAVINNG子句：避免使用HHAVINGG子句, HHAVINGG 只會(huì)在檢檢索出所有記記錄之后才對(duì)對(duì)結(jié)果集進(jìn)行行過濾. 這這個(gè)處理需要要排序,總計(jì)計(jì)等操作. 如果能通過過WHEREE子句限制記記錄的數(shù)目,那就能減少少這方面的

6、開開銷. (非非oraclle中)onn、wherre、havving這三三個(gè)都可以加加條件的子句句中，on是是最先執(zhí)行，wwhere次次之，havving最后后，因?yàn)閛nn是先把不符符合條件的記記錄過濾后才才進(jìn)行統(tǒng)計(jì)，它它就可以減少少中間運(yùn)算要要處理的數(shù)據(jù)據(jù)，按理說應(yīng)應(yīng)該速度是最最快的，whhere也應(yīng)應(yīng)該比havving快點(diǎn)點(diǎn)的，因?yàn)樗^濾數(shù)據(jù)后后才進(jìn)行suum，在兩個(gè)個(gè)表聯(lián)接時(shí)才才用on的，所所以在一個(gè)表表的時(shí)候，就就剩下wheere跟haaving比比較了。在這這單表查詢統(tǒng)統(tǒng)計(jì)的情況下下，如果要過過濾的條件沒沒有涉及到要要計(jì)算字段，那那它們的結(jié)果果是一樣的，只只是wherre可以使用

7、用rushmmore技術(shù)術(shù)，而havving就不不能，在速度度上后者要慢慢如果要涉及及到計(jì)算的字字段，就表示示在沒計(jì)算之之前，這個(gè)字字段的值是不不確定的，根根據(jù)上篇寫的的工作流程，wwhere的的作用時(shí)間是是在計(jì)算之前前就完成的，而而havinng就是在計(jì)計(jì)算后才起作作用的，所以以在這種情況況下，兩者的的結(jié)果會(huì)不同同。在多表聯(lián)聯(lián)接查詢時(shí)，oon比wheere更早起起作用。系統(tǒng)統(tǒng)首先根據(jù)各各個(gè)表之間的的聯(lián)接條件，把把多個(gè)表合成成一個(gè)臨時(shí)表表后，再由wwhere進(jìn)進(jìn)行過濾，然然后再計(jì)算，計(jì)計(jì)算完后再由由havinng進(jìn)行過濾濾。由此可見見，要想過濾濾條件起到正正確的作用，首首先要明白這這個(gè)條件應(yīng)該

8、該在什么時(shí)候候起作用，然然后再?zèng)Q定放放在那里（12） 減少對(duì)表的的查詢：在含有子查查詢的SQLL語句中,要要特別注意減減少對(duì)表的查查詢.例子： SELEECT TAAB_NAMME FROOM TABBLES WWHERE (TAB_NAME,DB_VEER) = ( SELLECTTAB_NNAME,DDB_VERR FROMM TAB_COLUMMNS WHHERE VVERSIOON = 6604)（13） 通過內(nèi)部函函數(shù)提高SQQL效率.：復(fù)雜的SQQL往往犧牲牲了執(zhí)行效率率. 能夠掌掌握上面的運(yùn)運(yùn)用函數(shù)解決決問題的方法法在實(shí)際工作作中是非常有有意義的（14） 使用表的別別名(Alii

9、as)：當(dāng)在SQLL語句中連接接多個(gè)表時(shí), 請(qǐng)使用表表的別名并把把別名前綴于于每個(gè)Collumn上.這樣一來,就可以減少少解析的時(shí)間間并減少那些些由Coluumn歧義引引起的語法錯(cuò)錯(cuò)誤.（15） 用EXISSTS替代IIN、用NOOT EXIISTS替代代NOT IIN：在許多基于于基礎(chǔ)表的查查詢中,為了了滿足一個(gè)條條件,往往需需要對(duì)另一個(gè)個(gè)表進(jìn)行聯(lián)接接.在這種情情況下, 使使用EXISSTS(或NNOT EXXISTS)通常將提高高查詢的效率率. 在子查查詢中,NOOT IN子子句將執(zhí)行一一個(gè)內(nèi)部的排排序和合并. 無論在哪哪種情況下,NOT IIN都是最低低效的 (因因?yàn)樗鼘?duì)子查查詢中的表執(zhí)

10、執(zhí)行了一個(gè)全全表遍歷). 為了避免免使用NOTT IN ,我們可以把把它改寫成外外連接(Ouuter JJoins)或NOT EXISTTS.例子：（高效）SSELECTT * FRROM EMMP (基礎(chǔ)礎(chǔ)表) WHHERE EEMPNO 0 AAND EXXISTS (SELEECT X FROMM DEPTT WHERRE DEPPT.DEPPTNO = EMP.DEPTNNO ANDD LOC = MELB)(低效)SSELECTT * FRROM EMMP (基礎(chǔ)礎(chǔ)表) WHHERE EEMPNO 0 AAND DEEPTNO IN(SEELECT DEPTNNO FROOM DEP

11、PT WHEERE LOOC = MELB)（16） 識(shí)別低效執(zhí)行的SQL語語句：雖然目前各各種關(guān)于SQQL優(yōu)化的圖圖形化工具層層出不窮,但但是寫出自己己的SQL工工具來解決問問題始終是一一個(gè)最好的方方法：SELECCT EXEECUTIOONS , DISK_READSS, BUFFFER_GGETS, ROUNDD(BUFFFER_GGETS-DDISK_RREADS)/BUFFFER_GEETS,2) Hit_radioo, ROUNDD(DISKK_READDS/EXEECUTIOONS,2) Readds_perr_run, SQL_TTEXT FROM V$SQLLAREA WHE

12、REE EXECCUTIONNS0 AND BBUFFERR_GETSS 0 AND (BUFFEER_GETTS-DISSK_REAADS)/BBUFFERR_GETSS 0.8 ORDERR BY 44 DESCC;（17） 用索引提高高效率：索引是表的的一個(gè)概念部部分,用來提提高檢索數(shù)據(jù)據(jù)的效率，OORACLEE使用了一個(gè)個(gè)復(fù)雜的自平平衡B-trree結(jié)構(gòu). 通常,通通過索引查詢?cè)償?shù)據(jù)比全表表掃描要快. 當(dāng)ORAACLE找出出執(zhí)行查詢和和Updatte語句的最最佳路徑時(shí), ORACCLE優(yōu)化器器將使用索引引. 同樣在在聯(lián)結(jié)多個(gè)表表時(shí)使用索引引也可以提高高效率. 另另一個(gè)使用索索引的好處是

13、是,它提供了了主鍵(prrimaryy key)的唯一性驗(yàn)驗(yàn)證.。那些些LONG或或LONG RAW數(shù)據(jù)據(jù)類型, 你你可以索引幾幾乎所有的列列. 通常, 在大型表表中使用索引引特別有效. 當(dāng)然,你你也會(huì)發(fā)現(xiàn), 在掃描小小表時(shí),使用用索引同樣能能提高效率. 雖然使用用索引能得到到查詢效率的的提高,但是是我們也必須須注意到它的的代價(jià). 索索引需要空間間來存儲(chǔ),也也需要定期維維護(hù), 每當(dāng)當(dāng)有記錄在表表中增減或索索引列被修改改時(shí), 索引引本身也會(huì)被被修改. 這這意味著每條條記錄的INNSERT , DELLETE , UPDAATE將為此此多付出4 , 5 次次的磁盤I/O . 因因?yàn)樗饕枰~外的

14、存儲(chǔ)儲(chǔ)空間和處理理,那些不必必要的索引反反而會(huì)使查詢?cè)兎磻?yīng)時(shí)間變變慢.。定期期的重構(gòu)索引引是有必要的的.：ALTERR INDEEX RREBUILLD （18） 用EXISSTS替換DDISTINNCT：當(dāng)提交一個(gè)個(gè)包含一對(duì)多多表信息(比比如部門表和和雇員表)的的查詢時(shí),避避免在SELLECT子句句中使用DIISTINCCT. 一般般可以考慮用用EXISTT替換, EEXISTSS 使查詢更更為迅速,因因?yàn)镽DBMMS核心模塊塊將在子查詢?cè)兊臈l件一旦旦滿足后,立立刻返回結(jié)果果. 例子： (低效): SELECCT DISSTINCTT DEPTT_NO,DDEPT_NNAME FFROM D

15、DEPT DD , EMMP E WHEREE D.DEEPT_NOO = E.DEPT_NO (高效): SELECCT DEPPT_NO,DEPT_NAME FROM DEPT D WHEERE EXXISTS ( SELLECT X FROM EMP EE WHERRE E.DDEPT_NNO = DD.DEPTT_NO);（19） sql語句句用大寫的；因?yàn)閛raacle總是是先解析sqql語句，把把小寫的字母母轉(zhuǎn)換成大寫寫的再執(zhí)行（20） 在javaa代碼中盡量量少用連接符符“”連接字符串串?。?1） 避免在索引引列上使用NNOT 通常常，我們要避免免在索引列上上使用NOTT, NO

16、TT會(huì)產(chǎn)生在和和在索引列上上使用函數(shù)相相同的影響. 當(dāng)ORAACLE”遇到”NOT,他他就會(huì)停止使使用索引轉(zhuǎn)而而執(zhí)行全表掃掃描.（22） 避免在索引引列上使用計(jì)計(jì)算WHEREE子句中，如如果索引列是是函數(shù)的一部部分優(yōu)化器器將不使用索索引而使用全全表掃描 舉例: 低效： SELECCT FROMM DEPTT WHERRE SALL * 122 255000; 高效: SELECCT FROMM DEPTT WHERRE SALL 255000/112;（23） 用=替代代高效: SELECCT * FFROM EEMP WHHERE DDEPTNOO =4 低效: SELECCT * FFRO

17、M EEMP WHHERE DDEPTNOO 3 兩者的區(qū)別別在于, 前前者DBMSS將直接跳到到第一個(gè)DEEPT等于44的記錄而后后者將首先定定位到DEPPTNO=33的記錄并且且向前掃描到到第一個(gè)DEEPT大于33的記錄.（24） 用UNIOON替換ORR (適用于于索引列)通常情況下下, 用UNNION替換換WHEREE子句中的OOR將會(huì)起到到較好的效果果. 對(duì)索引引列使用ORR將造成全表表掃描. 注注意, 以上上規(guī)則只針對(duì)對(duì)多個(gè)索引列列有效. 如如果有collumn沒有有被索引, 查詢效率可可能會(huì)因?yàn)槟隳銢]有選擇OOR而降低. 在下面的的例子中, LOC_IID 和REEGION上上都

18、建有索引引. 高效: SELECCT LOCC_ID , LOC_DESC , REGGION FROM LOCATTION WHEREE LOC_ID = 10 UNIONN SELECCT LOCC_ID , LOC_DESC , REGGION FROM LOCATTION WHEREE REGIION = “MELBOOURNE” 低效: SELECCT LOCC_ID , LOC_DESC , REGGION FROM LOCATTION WHEREE LOC_ID = 10 ORR REGIION = “MELBOOURNE” 如果你堅(jiān)持持要用OR, 那就需要要返回記錄最最少的索引

19、列列寫在最前面面.（25） 用IN來替替換OR 這是一條簡(jiǎn)簡(jiǎn)單易記的規(guī)規(guī)則，但是實(shí)實(shí)際的執(zhí)行效效果還須檢驗(yàn)驗(yàn)，在ORAACLE8ii下，兩者的的執(zhí)行路徑似似乎是相同的的低效: SELECCT. FROOM LOCCATIONN WHERRE LOCC_ID = 10 OOR LOCC_ID = 20 OOR LOCC_ID = 30 高效 SELECCT FROMM LOCAATION WHEREE LOC_IN INN (10,20,300);（26） 避免在索引引列上使用IIS NULLL和IS NOT NNULL避免在索引引中使用任何何可以為空的的列，ORAACLE將無無法使用該索索引對(duì)

20、于單單列索引，如如果列包含空空值，索引中中將不存在此此記錄. 對(duì)對(duì)于復(fù)合索引引，如果每個(gè)個(gè)列都為空，索索引中同樣不不存在此記錄錄.如果至至少有一個(gè)列列不為空，則則記錄存在于于索引中舉舉例: 如果果唯一性索引引建立在表的的A列和B列列上, 并且且表中存在一一條記錄的AA,B值為(123,nnull) , ORAACLE將不不接受下一條條具有相同AA,B值（1123,nuull）的記記錄(插入). 然而如如果所有的索索引列都為空空，ORACCLE將認(rèn)為為整個(gè)鍵值為為空而空不等等于空. 因因此你可以插插入10000 條具有相相同鍵值的記記錄,當(dāng)然它它們都是空! 因?yàn)榭罩抵挡淮嬖谟谒魉饕兄?所所以W

21、HERRE子句中對(duì)對(duì)索引列進(jìn)行行空值比較將將使ORACCLE停用該該索引.低效: (索引失效) SELECCT FROMM DEPAARTMENNT WHEERE DEEPT_COODE ISS NOT NULL; 高效: (索引有效) SELECCT FROMM DEPAARTMENNT WHEERE DEEPT_COODE =0;（27） 總是使用索索引的第一個(gè)個(gè)列：如果索引是是建立在多個(gè)個(gè)列上, 只只有在它的第第一個(gè)列(lleadinng collumn)被被wheree子句引用時(shí)時(shí),優(yōu)化器才才會(huì)選擇使用用該索引. 這也是一條條簡(jiǎn)單而重要要的規(guī)則，當(dāng)當(dāng)僅引用索引引的第二個(gè)列列時(shí),優(yōu)化器器

22、使用了全表表掃描而忽略略了索引（28） 用UNIOON-ALLL 替換UNNION ( 如果有可可能的話)：當(dāng)SQL語語句需要UNNION兩個(gè)個(gè)查詢結(jié)果集集合時(shí),這兩兩個(gè)結(jié)果集合合會(huì)以UNIION-ALLL的方式被被合并, 然然后在輸出最最終結(jié)果前進(jìn)進(jìn)行排序. 如果用UNNION AALL替代UUNION, 這樣排序序就不是必要要了. 效率率就會(huì)因此得得到提高. 需要注意的的是，UNIION ALLL 將重復(fù)復(fù)輸出兩個(gè)結(jié)結(jié)果集合中相相同記錄. 因此各位還還是要從業(yè)務(wù)務(wù)需求分析使使用UNIOON ALLL的可行性. UNIOON 將對(duì)結(jié)結(jié)果集合排序序,這個(gè)操作作會(huì)使用到SSORT_AAREA_S

23、SIZE這塊塊內(nèi)存. 對(duì)對(duì)于這塊內(nèi)存存的優(yōu)化也是是相當(dāng)重要的的. 下面的的SQL可以以用來查詢排排序的消耗量量低效： SELECCT ACCCT_NUMM, BALLANCE_AMT FROM DEBITT_TRANNSACTIIONS WHEREE TRANN_DATEE = 31-DEEC-95 UNIONN SELECCT ACCCT_NUMM, BALLANCE_AMT FROM DEBITT_TRANNSACTIIONS WHEREE TRANN_DATEE = 31-DEEC-95 高效: SELECCT ACCCT_NUMM, BALLANCE_AMT FROM DEBITT_T

24、RANNSACTIIONS WHEREE TRANN_DATEE = 31-DEEC-95 UNIONN ALL SELECCT ACCCT_NUMM, BALLANCE_AMT FROM DEBITT_TRANNSACTIIONS WHEREE TRANN_DATEE = 31-DEEC-95（29） 用WHERRE替代ORRDER BBY：ORDERR BY 子子句只在兩種種嚴(yán)格的條件件下使用索引引. ORDERR BY中所所有的列必須須包含在相同同的索引中并并保持在索引引中的排列順順序. ORDERR BY中所所有的列必須須定義為非空空. WHEREE子句使用的的索引和ORRDER BB

25、Y子句中所所使用的索引引不能并列.例如: 表DEPTT包含以下列列: DEPT_CODE PK NOOT NULLL DEPT_DESC NOT NNULL DEPT_TYPE NULL低效: (索引不被使使用) SELECCT DEPPT_CODDE FROOM DEPPT ORDDER BYY DEPTT_TYPEE 高效: (使用索引) SELECCT DEPPT_CODDE FROOM DEPPT WHEERE DEEPT_TYYPE 0（30） 避免改變索索引列的類型型.:當(dāng)比較不同同數(shù)據(jù)類型的的數(shù)據(jù)時(shí), ORACLLE自動(dòng)對(duì)列列進(jìn)行簡(jiǎn)單的的類型轉(zhuǎn)換. 假設(shè) EMMPNO是一一個(gè)數(shù)值

26、類型型的索引列. SELECCT FROMM EMP WHEREE EMPNNO = 123 實(shí)際上,經(jīng)經(jīng)過ORACCLE類型轉(zhuǎn)轉(zhuǎn)換, 語句句轉(zhuǎn)化為: SELECCT FROMM EMP WHEREE EMPNNO = TTO_NUMMBER(123) 幸運(yùn)的是,類型轉(zhuǎn)換沒沒有發(fā)生在索索引列上,索索引的用途沒沒有被改變. 現(xiàn)在,假設(shè)設(shè)EMP_TTYPE是一一個(gè)字符類型型的索引列. SELECCT FROMM EMP WHEREE EMP_TYPE = 1233 這個(gè)語句被被ORACLLE轉(zhuǎn)換為: SELECCT FROMM EMP WHEREETO_NUUMBER(EMP_TTYPE)=123 因?yàn)閮?nèi)部發(fā)發(fā)生的類型轉(zhuǎn)轉(zhuǎn)換, 這個(gè)個(gè)索引將不會(huì)會(huì)被用到! 為了避免OORACLEE對(duì)你的SQQL進(jìn)行隱式式的類型轉(zhuǎn)換換, 最好把把類型轉(zhuǎn)換用用顯式表現(xiàn)出出來. 注意意當(dāng)字符和數(shù)數(shù)值比較時(shí), ORACCLE會(huì)優(yōu)先先轉(zhuǎn)換數(shù)值類類型到字符類類型（31） 需要當(dāng)心的的WHEREE子句:某些SELLECT 語語句中的WHHERE子句句不使用