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

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

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

3、00（6） 使使用DECOODE函數(shù)來來減少處理時時間：使用DECCODE函數(shù)數(shù)可以避免重重復掃描相同同記錄或重復復連接相同的的表.（7） 整整合簡單,無無關聯(lián)的數(shù)據(jù)據(jù)庫訪問：如果你有幾幾個簡單的數(shù)數(shù)據(jù)庫查詢語語句,你可以以把它們整合合到一個查詢詢中(即使它它們之間沒有有關系)（8） 刪刪除重復記錄錄：最高效的刪刪除重復記錄錄方法 ( 因為使用了了ROWIDD)例子：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：當刪除表中中的記錄時,在通常情況況下, 回滾滾段(rolllbackk segmments ) 用來存存放可以被恢恢復的信息. 如果你沒沒有COMMMIT事務,ORACLLE會將數(shù)據(jù)據(jù)恢復到刪除除之前的狀態(tài)態(tài)(準確地說說是恢復到執(zhí)執(zhí)行刪除命令令之前的狀況況) 而當運運用TRUNNCATE時時, 回滾段段不再存放任任何可被恢復復的信息.當當命令運行后后,數(shù)據(jù)不能能被恢復.因因此很少的資資源被調用,執(zhí)行時間也也會很短. (譯者按: TRUNNCATE只只在刪除全表表適用,TRRUNCATTE是DDLL不是DMLL)（10） 盡量多使用用COMMIIT：只要有可能能,

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

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

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

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

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

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

11、PT WHEERE LOOC = MELB)（16） 識別低效執(zhí)行的SQL語語句：雖然目前各各種關于SQQL優(yōu)化的圖圖形化工具層層出不窮,但但是寫出自己己的SQL工工具來解決問問題始終是一一個最好的方方法：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） 用索引提高高效率：索引是表的的一個概念部部分,用來提提高檢索數(shù)據(jù)據(jù)的效率，OORACLEE使用了一個個復雜的自平平衡B-trree結構. 通常,通通過索引查詢詢數(shù)據(jù)比全表表掃描要快. 當ORAACLE找出出執(zhí)行查詢和和Updatte語句的最最佳路徑時, ORACCLE優(yōu)化器器將使用索引引. 同樣在在聯(lián)結多個表表時使用索引引也可以提高高效率. 另另一個使用索索引的好處是

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

14、存儲儲空間和處理理,那些不必必要的索引反反而會使查詢詢反應時間變變慢.。定期期的重構索引引是有必要的的.：ALTERR INDEEX RREBUILLD （18） 用EXISSTS替換DDISTINNCT：當提交一個個包含一對多多表信息(比比如部門表和和雇員表)的的查詢時,避避免在SELLECT子句句中使用DIISTINCCT. 一般般可以考慮用用EXISTT替換, EEXISTSS 使查詢更更為迅速,因因為RDBMMS核心模塊塊將在子查詢詢的條件一旦旦滿足后,立立刻返回結果果. 例子： (低效): 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語句句用大寫的；因為oraacle總是是先解析sqql語句，把把小寫的字母母轉換成大寫寫的再執(zhí)行（20） 在javaa代碼中盡量量少用連接符符“”連接字符串串?。?1） 避免在索引引列上使用NNOT 通常常，我們要避免免在索引列上上使用NOTT, NO

16、TT會產(chǎn)生在和和在索引列上上使用函數(shù)相相同的影響. 當ORAACLE”遇到”NOT,他他就會停止使使用索引轉而而執(zhí)行全表掃掃描.（22） 避免在索引引列上使用計計算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將直接跳到到第一個DEEPT等于44的記錄而后后者將首先定定位到DEPPTNO=33的記錄并且且向前掃描到到第一個DEEPT大于33的記錄.（24） 用UNIOON替換ORR (適用于于索引列)通常情況下下, 用UNNION替換換WHEREE子句中的OOR將會起到到較好的效果果. 對索引引列使用ORR將造成全表表掃描. 注注意, 以上上規(guī)則只針對對多個索引列列有效. 如如果有collumn沒有有被索引, 查詢效率可可能會因為你你沒有選擇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” 如果你堅持持要用OR, 那就需要要返回記錄最最少的索引

19、列列寫在最前面面.（25） 用IN來替替換OR 這是一條簡簡單易記的規(guī)規(guī)則，但是實實際的執(zhí)行效效果還須檢驗驗，在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將無無法使用該索索引對

20、于單單列索引，如如果列包含空空值，索引中中將不存在此此記錄. 對對于復合索引引，如果每個個列都為空，索索引中同樣不不存在此記錄錄.如果至至少有一個列列不為空，則則記錄存在于于索引中舉舉例: 如果果唯一性索引引建立在表的的A列和B列列上, 并且且表中存在一一條記錄的AA,B值為(123,nnull) , ORAACLE將不不接受下一條條具有相同AA,B值（1123,nuull）的記記錄(插入). 然而如如果所有的索索引列都為空空，ORACCLE將認為為整個鍵值為為空而空不等等于空. 因因此你可以插插入10000 條具有相相同鍵值的記記錄,當然它它們都是空! 因為空值值不存在于索索引列中,所所以W

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

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

23、SIZE這塊塊內存. 對對于這塊內存存的優(yōu)化也是是相當重要的的. 下面的的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 子子句只在兩種種嚴格的條件件下使用索引引. 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） 避免改變索索引列的類型型.:當比較不同同數(shù)據(jù)類型的的數(shù)據(jù)時, ORACLLE自動對列列進行簡單的的類型轉換. 假設 EMMPNO是一一個數(shù)值

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