()選擇最有效率的表名順序(只在基於規則的優化器中有效)
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名FROM子句中寫在最後的表(基礎表 driving table)將被最先處理在FROM子句中包含多個表的情況下你必須選擇記錄條數最少的表作為基礎表如果有 個以上的表連接查詢 那就需要選擇交叉表 (intersection table)作為基礎表交叉表是指那個被其他表所引用的表
() WHERE子句中的連接順序 ORACLE采用自下而上的順序解析WHERE子句根據這個原理表之間的連接必須寫在其他 WHERE條件之前 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾
() SELECT子句中避免使用 ‘ * ‘ ORACLE 在解析的過程中 會將*依次轉換成所有的列名這個工作是通過查詢數據字典完成的 這意味著將耗費更多的時間
()減少訪問數據庫的次數 ORACLE在內部執行了許多工作: 解析 SQL 語句估算索引的利用率 綁定變量 讀數據塊等;
()在SQL*Plus SQL*Forms和Pro*C 中重新設置ARRAYSIZE 參數 可以增加每次數據庫訪問的檢索數據量 建議值為
()使用DECODE函數來減少處理時間 使用DECODE 函數可以避免重復掃描相同記錄或重復連接相同的表
()整合簡單無關聯的數據庫訪問 如果你有幾個簡單的數據庫查詢語句你可以把它們整合到一個查詢中(即使它們之間沒有關系)
()刪除重復記錄 最高效的刪除重復記錄方法 (因為使用了ROWID)
例子DELETE FROM EMP E WHERE EROWID > (SELECT MIN(XROWID) FROM EMP X WHEREXEMP_NO = EEMP_NO);
()用TRUNCATE替代DELETE 當刪除表中的記錄時在通常情況下回滾段(rollbacksegments ) 用來存放可以被恢復的信息
如果你沒有COMMIT事務ORACLE 會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況) 而當運用 TRUNCATE時 回滾段不再存放任何可被恢復的信息當命令運行後數據不能被恢復因此很少的資源被調用執行時間也會很短
(譯者按:TRUNCATE只在刪除全表適用TRUNCATE 是DDL不是DML)
()盡量多使用COMMIT 只要有可能在程序中盡量多使用 COMMIT 這樣程序的性能得到提高需求也會因為 COMMIT 所釋放的資源而減少:
COMMIT所釋放的資源:
a回滾段上用於恢復數據的信息
b被程序語句獲得的鎖
c redo logbuffer中的空間
d ORACLE為管理上述種資源中的內部花費
()用Where子句替換HAVING 子句 避免使用 HAVING 子句 HAVING 只會在檢索出所有記錄之後才對結果集進行過濾
這個處理需要排序總計等操作如果能通過 WHERE 子句限制記錄的數目那就能減少這方面的 開銷
(非oracle中)onwherehaving這三個都可以加條件的子句中on 是最先執行where 次之having 最後因為on 是先把不符合條件的記錄過濾後才進行統計它就可以減少中間運算要處理的數據按理說應該速度是最快的where也應該比having 快點的因為它過濾數據後才進行sum在兩個表聯接時才用on的所以在一個表的時候就剩下where 跟 having比較了
在這單表查詢統計的情況下如果要過濾的條件沒有涉及到要計算字段那它們的結果是一樣的只是where可以使用rushmore 技術而having 就不能在速度上後者要慢如果要涉及到計算的字段就表示在沒計算之前這個字段的值是不確定的根據上篇寫的工作流程where的作用時間是在計算之前就完成的而having 就是在計算後才起作用的所以在這種情況下兩者的結果會不同在多表聯接查詢時on比 where 更早起作用系統首先根據各個表之間的聯接條件把多個表合成一個臨時表後再由where進行過濾然後再計算計算完後再由 having 進行過濾由此可見要想過濾條件起到正確的作用首先要明白這個條件應該在什麼時候起作用然後再決定放在那裡
()減少對表的查詢 在含有子查詢的SQL語句中要特別注意減少對表的查詢
例子SELECT TAB_NAME FROM TABLES WHERE (TAB_NAMEDB_VER) = ( SELECT TAB_NAMEDB_VERFROM TAB_COLUMNS WHERE VERSION = )
()通過內部函數提高SQL效率 復雜的 SQL 往往犧牲了執行效率能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的
()使用表的別名(Alias) 當在SQL語句中連接多個表時 請使用表的別名並把別名前綴於每個Column上這樣一來 就可以減少解析的時間並減少
那些由Column歧義引起的語法錯誤
()用EXISTS替代IN用NOTEXISTS 替代NOT IN 在許多基於基礎表的查詢中為了滿足一個條件往往需要對另一個表進行聯接在這種情況下使用EXISTS(或NOTEXISTS)通常將提高查詢的效率在子查詢中NOT IN子句將執行一個內部的排序和合並 無論在哪種情況下NOTIN 都是最低效的(因為它對子查詢中的表執行了一個全表遍歷)為了避免使用 NOT IN 我們可以把它改寫成外連接(OuterJoins)或 NOT EXISTS
例子 (高效)SELECT * FROM EMP (基礎表) WHERE EMPNO > AND EXISTS (SELECT‘X FROM DEPT WHERE DEPTDEPTNO =EMPDEPTNO AND LOC = ‘MELB)
(低效)SELECT* FROM EMP (基礎表) WHERE EMPNO > AND DEPTNOIN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB)
()識別低效執行的SQL語句雖然目前各種關於SQL優化的圖形化工具層出不窮但是寫出自己的SQL工具來解決問題始終是一個最好的方法
SELECTEXECUTIONS DISK_READS BUFFER_GETSROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS) Hit_radioROUND(DISK_READS/EXECUTIONS)
Reads_per_runSQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS> AND BUFFER_GETS > AND(BUFFER_GETSDISK_READS)/BUFFER_GETS < ORDER BY DESC;
) 用索引提高效率 索引是表的一個概念部分用來提高檢索數據的效率ORACLE使用了一個復雜的自平衡 Btree 結構 通常通過索引查詢數據比全表掃描要快
當ORACLE找出執行查詢和 Update 語句的最佳路徑時ORACLE優化器將使用索引 同樣在聯結多個表時使用索引也可以提高效率
另一個使用索引的好處是它提供了主鍵(primary key)的唯一性驗證那些 LONG 或 LONG RAW數據類型 你可以索引幾乎所有的列
通常在大型表中使用索引特別有效 當然你也會發現在掃描小表時使用索引同樣能提高效率 雖然使用索引能得到查詢效率的提高但是我們也必須注意到它的代價索引需要空間來存儲也需要定期維護 每當有記錄在表中增減或索引列被修改時索引本身也會被修改
這意味著每條記錄的 INSERT DELETE UPDATE將為此多付出 次的磁盤I/O 因為索引需要額外的存儲空間和處理 那些不必要的索引反而會使查詢反應時間變慢
定期的重構索引是有必要的 ALTER INDEX REBUILD
) 用EXISTS替換DISTINCT 當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時避免在 SELECT子句中使用 DISTINCT 一般可以考慮用EXIST 替換EXISTS 使查詢更為迅速因為RDBMS核心模塊將在子查詢的條件一旦滿足後立刻返回結果例子
(低效):SELECT DISTINCT DEPT_NODEPT_NAME FROM DEPT D EMP E WHERE DDEPT_NO =EDEPT_NO
(高效):SELECT DEPT_NODEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT „X FROM EMP E WHERE EDEPT_NO = DDEPT_NO);
() sql語句用大寫的;因為oracle 總是先解析sql 語句把小寫的字母轉換成大寫的再執行
()在java代碼中盡量少用連接符“+”連接字符串!
()避免在索引列上使用NOT通常 我們要避免在索引列上使用 NOT NOT會產生在和在索引列上使用函數相同的影響當 ORACLE”遇到”NOT他就會停止使用索引
轉而執行全表掃描
()避免在索引列上使用計算 WHERE子句中如果索引列是函數的一部分優化器將不使用索引而使用全表掃描 舉例:
低效SELECT… FROM DEPT WHERE SAL * > ;
高效:SELECT… FROM DEPT WHERE SAL > /;
()用>=替代>
高效:SELECT * FROM EMP WHERE DEPTNO >=
低效:SELECT * FROM EMP WHERE DEPTNO >兩者的區別在於 前者 DBMS 將直接跳到第一個 DEPT等於 的記錄而後者將首先定位到 DEPTNO= 的記錄並且向前掃描
到第一個DEPT大於的記錄
()用UNION替換OR (適用於索引列) 通常情況下用UNION 替換WHERE 子句中的OR將會起到較好的效果 對索引列使用OR 將造成全表掃描
注意以上規則只針對多個索引列有效 如果有column 沒有被索引 查詢效率可能會因為你沒有選擇OR 而降低在下面的例子中LOC_ID 和REGION 上都建有索引
高效:SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE LOC_ID = UNION SELECTLOC_ID LOC_DESC
REGION FROMLOCATION WHERE REGION =“MELBOURNE” 低效: SELECTLOC_ID LOC_DESC REGION FROM LOCATION WHERE LOC_ID = OR REGION =“MELBOURNE”
如果你堅持要用OR那就需要返回記錄最少的索引列寫在最前面
()用IN來替換OR 這是一條簡單易記的規則但是實際的執行效果還須檢驗在 ORACLEi下兩者的執行路徑似乎是相同的
低效:SELECT… FROM LOCATION WHERE LOC_ID = OR LOC_ID = OR LOC_ID =
高效SELECT… FROM LOCATION WHERE LOC_IN IN ();
()避免在索引列上使用IS NULL和IS NOT NULL 避免在索引中使用任何可以為空的列ORACLE將無法使用該索引對於單列索引如果列包含空值
索引中將不存在此記錄對於復合索引如果每個列都為空索引中同樣不存在此記錄 如果至少有一個列不為空則記錄存在於索引中
舉例:如果唯一性索引建立在表的A 列和B 列上並且表中存在一條記錄的AB 值為(null) ORACLE將不接受下一條具有相同 AB 值(null)的記錄(插入)
然而如果所有的索引列都為空ORACLE將認為整個鍵值為空而空不等於空 因此你可以插入 條具有相同鍵值的記錄當然它們都是空! 因為空值不存在於索引列中
所以WHERE子句中對索引列進行空值比較將使 ORACLE 停用該索引
低效: (索引失效) SELECT… FROM DEPARTMENT WHERE DEPT_CODEIS NOT NULL;
高效: (索引有效) SELECT… FROM DEPARTMENT WHERE DEPT_CODE>=;
()總是使用索引的第一個列 如果索引是建立在多個列上只有在它的第一個列(leadingcolumn)被where子句引用時優化器才會選擇使用該索引
這也是一條簡單而重要的規則當僅引用索引的第二個列時優化器使用了全表掃描而忽略了索引
) 用UNIONALL替換UNION ( 如果有可能的話) 當SQL 語句需要UNION兩個查詢結果集合時這兩個結果集合會以UNIONALL 的方式被 合並 然後在輸出最終結果前
進行排序如果用UNION ALL替代UNION 這樣排序就不是必要了 效率就會因此得到提高需要注意的是UNION ALL 將重復輸出兩個結果集合中相同記錄因此各位還是
要從業務需求分析使用 UNION ALL的可行性 UNION 將對結果集合排序這個操作會使用到 SORT_AREA_SIZE這塊內存 對於這塊內存的優化也是相當重要的
下面的SQL可以用來查詢排序的消耗量
低效SELECT ACCT_NUM BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE =DEC
UNIONSELECT ACCT_NUM BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE =DEC
高效:SELECT ACCT_NUM BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE =DEC
UNION ALLSELECT ACCT_NUM BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE =DEC
()用WHERE替代ORDER BY ORDER BY 子句只在兩種嚴格的條件下使用索引 ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序
ORDER BY中所有的列必須定義為非空 WHERE子句使用的索引和ORDER BY 子句中所使用的索引不能並列
例如:表DEPT 包含以下列: DEPT_CODE PK NOT NULL DEPT_DESCNOT NULL DEPT_TYPE NULL
低效: (索引不被使用) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
高效: (使用索引) SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE >
()避免改變索引列的類型:當比較不同數據類型的數據時 ORACLE 自動對列進行簡單的類型轉換假設 EMPNO 是一個數值類型的索引列
SELECT…FROM EMP WHERE EMPNO =„ 實際上經過ORACLE類型轉換 語句轉化為: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(„)
幸運的是類型轉換沒有發生在索引列上索引的用途沒有被改變現在假設EMP_TYPE 是一個字符類型的索引列
SELECT…FROM EMP WHERE EMP_TYPE = 這個語句被ORACLE 轉換為: SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=因為內部發生的類型轉換這個索引將不會被用到!為了避免ORACLE 對你的SQL 進行隱式的類型轉換最好把類型轉換用顯式表現出來 注意當字符和數值比較時 ORACLE會優先
轉換數值類型到字符類型
()需要當心的WHERE子句: 某些SELECT 語句中的WHERE子句不使用索引 這裡有一些例子 在下面的例子裡()‘!=將不使用索引
記住索引只能告訴你什麼存在於表中 而不能告訴你什麼不存在於表中 ()‘||是字符連接函數 就象其他函數那樣停用了索引 ()‘+ 是數學函數
就象其他數學函數那樣停用了索引 ()相同的索引列不能互相比較這將會啟用全表掃描
() a如果檢索數據量超過%的表中記錄數使用索引將沒有顯著的效率提高 b在特定情況下 使用索引也許會比全表掃描慢 但這是同一個數量級上的區別
而通常情況下使用索引比全表掃描要塊幾倍乃至幾千倍!
()避免使用耗費資源的操作:帶有DISTINCTUNIONMINUSINTERSECTORDERBY 的SQL語句會啟動SQL 引擎 執行耗費資源的排序(SORT)功能
DISTINCT需要一次排序操作而其他的至少需要執行兩次排序 通常帶有UNION MINUS INTERSECT的SQL語句都可以用其他方式重寫 如果你的數據庫的SORT_AREA_SIZE調配得好使用UNION MINUSINTERSECT 也是可以考慮的畢竟它們的可讀性很強
()優化GROUP BY:提高GROUP BY 語句的效率 可以通過將不需要的記錄在GROUP BY之前過濾掉下面兩個查詢返回相同結果但第二個明顯就快了許多
低效:SELECT JOB AVG(SAL) FROM EMP GROUP by JOB HAVING JOB =„PRESIDENT OR JOB = „MANAGER
高效:SELECT JOB AVG(SAL) FROM EMP WHERE JOB =„PRESIDENTOR JOB = „MANAGER GROUP by JOB
From:http://tw.wingwit.com/Article/program/Oracle/201311/19060.html
