（） 選擇最有效率的表名順序(只在基於規則的優化器中有效) 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 WHERE XEMP_NO = EEMP_NO); （） 用TRUNCATE替代DELETE 當刪除表中的記錄時在通常情況下 回滾段(rollback segments ) 用來存放可以被恢復的信息
如果你沒有COMMIT事務ORACLE會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況) 而當運用TRUNCATE時 回滾段不再存放任何可被恢復的信息當命令運行後數據不能被恢復因此很少的資源被調用執行時間也會很短
(譯者按: TRUNCATE只在刪除全表適用TRUNCATE是DDL不是DML) （） 盡量多使用COMMIT 只要有可能在程序中盡量多使用COMMIT 這樣程序的性能得到提高需求也會因為COMMIT所釋放的資源而減少: COMMIT所釋放的資源: a
回滾段上用於恢復數據的信息
b
被程序語句獲得的鎖 c
redo log buffer 中的空間 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_VER FROM TAB_COLUMNS WHERE VERSION = )
（） 通過內部函數提高SQL效率
復雜的SQL往往犧牲了執行效率
能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的
（） 使用表的別名(Alias) 當在SQL語句中連接多個表時 請使用表的別名並把別名前綴於每個Column上這樣一來就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤
（） 用EXISTS替代IN用NOT EXISTS替代NOT IN 在許多基於基礎表的查詢中為了滿足一個條件往往需要對另一個表進行聯接在這種情況下 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率
在子查詢中NOT IN子句將執行一個內部的排序和合並
無論在哪種情況下NOT IN都是最低效的
(因為它對子查詢中的表執行了一個全表遍歷)
為了避免使用NOT IN 我們可以把它改寫成外連接(Outer Joins)或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 DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = MELB)
（） 識別低效執行的SQL語句 雖然目前各種關於SQL優化的圖形化工具層出不窮但是寫出自己的SQL工具來解決問題始終是一個最好的方法 SELECT EXECUTIONS DISK_READS BUFFER_GETS ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS) Hit_radio ROUND(DISK_READS/EXECUTIONS) Reads_per_run SQL_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 <INDEXNAME> REBUILD <TABLESPACENAME>
（） 用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 SELECT LOC_ID LOC_DESC REGION FROM LOCATION WHERE REGION = MELBOURNE 低效: SELECT LOC_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_CODE IS NOT NULL; 高效:
(索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=;
（） 總是使用索引的第一個列 如果索引是建立在多個列上 只有在它的第一個列(leading column)被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 UNION SELECT ACCT_NUM BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = DEC 高效: SELECT ACCT_NUM BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = DEC UNION ALL SELECT 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_DESC NOT 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
在特定情況下 使用索引也許會比全表掃描慢 但這是同一個數量級上的區別
而通常情況下使用索引比全表掃描要塊幾倍乃至幾千倍!
（） 避免使用耗費資源的操作: 帶有DISTINCTUNIONMINUSINTERSECTORDER BY的SQL語句會啟動SQL引擎 執行耗費資源的排序(SORT)功能
DISTINCT需要一次排序操作 而其他的至少需要執行兩次排序
通常 帶有UNION MINUS INTERSECT的SQL語句都可以用其他方式重寫
如果你的數據庫的SORT_AREA_SIZE調配得好 使用UNION MINUS INTERSECT也是可以考慮的 畢竟它們的可讀性很強
（） 優化GROUP BY: 提高GROUP BY 語句的效率 可以通過將不需要的記錄在GROUP BY 之前過濾掉下面兩個查詢返回相同結果但第二個明顯就快了許多
低效: SELECT JOB AVG(SAL) FROM EMP GROUP JOB HAVING JOB = PRESIDENT OR JOB = MANAGER 高效: SELECT JOB AVG(SAL) FROM EMP WHERE JOB = PRESIDENT OR JOB = MANAGER GROUP JOB
From:http://tw.wingwit.com/Article/program/Oracle/201311/17488.html
