Oracle語句優化規則匯總(10)

需要當心的WHERE子句

　　某些SELECT 語句中的WHERE子句不使用索引 這裡有一些例子

　　在下面的例子裡 != 將不使用索引 記住 索引只能告訴你什麼存在於表中 而不能告訴你什麼不存在於表中

　　不使用索引

　　 　　SELECT ACCOUNT_NAME
　　FROM TRANSACTION
　　WHERE AMOUNT !=;

　　使用索引

　　 　　SELECT ACCOUNT_NAME
　　FROM TRANSACTION
　　WHERE AMOUNT >;

　　下面的例子中 ||是字符連接函數 就象其他函數那樣 停用了索引

　　不使用索引

　　 　　SELECT ACCOUNT_NAMEAMOUNT
　　FROM TRANSACTION
　　WHERE ACCOUNT_NAME||ACCOUNT_TYPE=AMEXA;

　　使用索引

　　 　　SELECT ACCOUNT_NAMEAMOUNT
　　FROM TRANSACTION
　　WHERE ACCOUNT_NAME = AMEXAND ACCOUNT_TYPE= A;

　　下面的例子中 +是數學函數 就象其他數學函數那樣 停用了索引

　　不使用索引

　　 　　SELECT ACCOUNT_NAME AMOUNT
　　FROM TRANSACTION
　　WHERE AMOUNT +  >;

　　使用索引

　　 　　SELECT ACCOUNT_NAME AMOUNT
　　FROM TRANSACTION
　　WHERE AMOUNT >  ;

　　下面的例子中相同的索引列不能互相比較這將會啟用全表掃描

　　不使用索引

　　 　　SELECT ACCOUNT_NAME AMOUNT
　　FROM TRANSACTION
　　WHERE ACCOUNT_NAME = NVL(ACC_NAMEACCOUNT_NAME);

　　使用索引

　　 　　SELECT ACCOUNT_NAME AMOUNT
　　FROM TRANSACTION
　　WHERE ACCOUNT_NAME LIKE NVL(ACC_NAME%);

　　如果一定要對使用函數的列啟用索引 ORACLE新的功能 基於函數的索引(FunctionBased Index) 也許是一個較好的方案

　　 　　CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基於函數的索引*/
　　SELECT * FROM emp WHERE UPPER(ename) = BLACKSNAIL; /*將使用索引*/

　　 連接多個掃描

　　如果你對一個列和一組有限的值進行比較 優化器可能執行多次掃描並對結果進行合並連接

　　舉例

　　 　　SELECT *
　　FROM LODGING
　　WHERE MANAGER IN (BILL GATESKEN MULLER);

　　優化器可能將它轉換成以下形式

　　 　　SELECT *
　　FROM LODGING
　　WHERE MANAGER = BILL GATESOR MANAGER = KEN MULLER;

　　當選擇執行路徑時 優化器可能對每個條件采用LODGING$MANAGER上的索引范圍掃描 返回的ROWID用來訪問LODGING表的記錄 (通過TABLE ACCESS BY ROWID 的方式) 最後兩組記錄以連接(CONCATENATION)的形式被組合成一個單一的集合

　　Explain Plan

　　 　　SELECT STATEMENT Optimizer=CHOOSE
　　CONCATENATION
　　TABLE ACCESS (BY INDEX ROWID) OF LODGING
　　INDEX (RANGE SCAN ) OF LODGING$MANAGER (NONUNIQUE)
　　TABLE ACCESS (BY INDEX ROWID) OF LODGING
　　INDEX (RANGE SCAN ) OF LODGING$MANAGER (NONUNIQUE)

　　本節和第節似乎有矛盾之處

　　 CBO下使用更具選擇性的索引

　　基於成本的優化器(CBO CostBased Optimizer)對索引的選擇性進行判斷來決定索引的使用是否能提高效率

　　如果索引有很高的選擇性 那就是說對於每個不重復的索引鍵值只對應數量很少的記錄

　　比如 表中共有條記錄而其中有個不重復的索引鍵值 這個索引的選擇性就是/ = 選擇性越高 通過索引鍵值檢索出的記錄就越少

　　如果索引的選擇性很低 檢索數據就需要大量的索引范圍查詢操作和ROWID 訪問表的操作 也許會比全表掃描的效率更低

　　下列經驗請參閱

　　a 如果檢索數據量超過%的表中記錄數使用索引將沒有顯著的效率提高

　　b 在特定情況下 使用索引也許會比全表掃描慢 但這是同一個數量級上的區別 而通常情況下使用索引比全表掃描要快幾倍乃至幾千倍!

　　 避免使用耗費資源的操作

　　帶有DISTINCTUNIONMINUSINTERSECTORDER BY的SQL語句會啟動SQL引擎執行耗費資源的排序(SORT)功能 DISTINCT需要一次排序操作 而其他的至少需要執行兩次排序

　　例如一個UNION查詢其中每個查詢都帶有GROUP BY子句 GROUP BY會觸發嵌入排序(NESTED SORT) ; 這樣 每個查詢需要執行一次排序 然後在執行UNION時 又一個唯一排序(SORT UNIQUE)操作被執行而且它只能在前面的嵌入排序結束後才能開始執行 嵌入的排序的深度會大大影響查詢的效率

　　通常 帶有UNION MINUS INTERSECT的SQL語句都可以用其他方式重寫

　　如果你的數據庫的SORT_AREA_SIZE調配得好 使用UNION MINUS INTERSECT也是可以考慮的 畢竟它們的可讀性很強

　　 優化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 = PRESIDENT
　　OR JOB = MANAGERGROUP by JOB

　　 使用日期當

　　使用日期是需要注意如果有超過位小數加到日期上 這個日期會進到下一天!

　　例如

　　

　　 　　SELECT TO_DATE(JAN+)
　　FROM DUAL;
　　ReturnsJAN 

　　

　　 　　SELECT TO_DATE(JAN+)
　　FROM DUAL;
　　ReturnsJAN 

　　雖然本節和SQL性能優化沒有關系 但是作者的功力可見一斑

　　 使用顯式的游標(CURSORs)

　　使用隱式的游標將會執行兩次操作 第一次檢索記錄 第二次檢查TOO MANY ROWS 這個exception 而顯式游標不執行第二次操作

　　 優化EXPORT和IMPORT

　　使用較大的BUFFER(比如MB )可以提高EXPORT和IMPORT的速度

　　ORACLE將盡可能地獲取你所指定的內存大小即使在內存不滿足也不會報錯這個值至少要和表中最大的列相當否則列值會被截斷

　　可以肯定的是 增加BUFFER會大大提高EXPORT IMPORT的效率 (曾經碰到過一個CASE 增加BUFFER後IMPORT/EXPORT快了倍!)

　　作者可能犯了一個錯誤 這個值至少要和表中最大的列相當否則列值會被截斷 其中最大的列也許是指最大的記錄大小

　　關於EXPORT/IMPORT的優化CSDN論壇中有一些總結性的貼子比如關於BUFFER參數 COMMIT參數等等 詳情請查

　　 分離表和索引

　　總是將你的表和索引建立在不同的表空間內(TABLESPACES) 決不要將不屬於ORACLE內部系統的對象存放到SYSTEM表空間裡 同時確保數據表空間和索引表空間置於不同的硬盤上

　　同時確保數據表空間和索引表空間置與不同的硬盤上可能改為如下更為准確 同時確保數據表空間和索引表空間置與不同的硬盤控制卡控制的硬盤上

　　

From:http://tw.wingwit.com/Article/program/Oracle/201311/17849.html