ORACLE SQL效能最佳化三

17. 使用表的別名(Alias)

18. 用EXISTS替代IN

19. 用NOT EXISTS替代NOT IN

20. 用表串連替換EXISTS

21. 用EXISTS替換DISTINCT

22. 識別'低效執行'的SQL語句

23. 使用TKPROF 工具來查詢SQL效能狀態

 

17. 使用表的別名(Alias)

當在SQL語句中串連多個表時, 請使用表的別名並把別名首碼於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.

(譯者注: Column歧義指的是由於SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬)

18. 用EXISTS替代IN

在許多基於基礎資料表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯結.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.

低效:

SELECT *

FROM EMP (基礎資料表)

WHERE EMPNO > 0

AND DEPTNO IN (SELECT DEPTNO

FROM DEPT

WHERE LOC = ‘MELB')

高效:

SELECT *

FROM EMP (基礎資料表)

WHERE EMPNO > 0

AND EXISTS (SELECT ‘X'

FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

AND LOC = ‘MELB')

(譯者按: 相對來說,用NOT EXISTS替換NOT IN 將更顯著地提高效率,下一節中將指出)

19. 用NOT EXISTS替代NOT IN

在子查詢中,NOT IN子句將執行一個內部的排序和合并. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍曆). 為了避免使用NOT IN ,我們可以把它改寫成外串連(Outer Joins)或NOT EXISTS.

例如:

SELECT …

FROM EMP

WHERE DEPT_NO NOT IN (SELECT DEPT_NO

FROM DEPT

WHERE DEPT_CAT='A');

為了提高效率.改寫為:

(方法一: 高效)

SELECT ….

FROM EMP A,DEPT B

WHERE A.DEPT_NO = B.DEPT(+)

AND B.DEPT_NO IS NULL

AND B.DEPT_CAT(+) = ‘A'

(方法二: 最高效)

SELECT ….

FROM EMP E

WHERE NOT EXISTS (SELECT ‘X'

FROM DEPT D

WHERE D.DEPT_NO = E.DEPT_NO

AND DEPT_CAT = ‘A');

20. 用表串連替換EXISTS

通常來說 , 採用表串連的方式比EXISTS更有效率

SELECT ENAME

FROM EMP E

WHERE EXISTS (SELECT ‘X'

FROM DEPT

WHERE DEPT_NO = E.DEPT_NO

AND DEPT_CAT = ‘A');

(更高效)

SELECT ENAME

FROM DEPT D,EMP E

WHERE E.DEPT_NO = D.DEPT_NO

AND DEPT_CAT = ‘A' ;

(譯者按: 在RBO的情況下,前者的執行路徑包括FILTER,後者使用NESTED LOOP)

21. 用EXISTS替換DISTINCT

當提交一個包含一對多表資訊(比如部門表和僱員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換

例如:

低效:

SELECT DISTINCT DEPT_NO,DEPT_NAME

FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO

高效:

SELECT DEPT_NO,DEPT_NAME

FROM DEPT D

WHERE EXISTS ( SELECT ‘X'

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

EXISTS 使查詢更為迅速,因為RDBMS核心模組將在子查詢的條件一旦滿足後,立刻返回結果.

22. 識別'低效執行'的SQL語句

用下列SQL工具找出低效SQL:

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,

ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,

ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,

SQL_TEXT

FROM V$SQLAREA

WHERE EXECUTIONS>0

AND BUFFER_GETS > 0

AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8

ORDER BY 4 DESC;

(譯者按: 雖然目前各種關於SQL最佳化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法)

23. 使用TKPROF 工具來查詢SQL效能狀態

SQL trace 工具收集正在執行的SQL的效能狀態資料並記錄到一個追蹤檔案中. 這個追蹤檔案提供了許多有用的資訊,例如解析次數.執行次數,CPU使用時間等.這些資料將可以用來最佳化你的系統.

設定SQL TRACE在會話層級: 有效

ALTER SESSION SET SQL_TRACE TRUE

設定SQL TRACE 在整個資料庫有效仿, 你必須將SQL_TRACE參數在init.ora中設為TRUE, USER_DUMP_DEST參數說明了產生追蹤檔案的目錄

(譯者按: 這一節中,作者並沒有提到TKPROF的用法, 對SQL TRACE的用法也不夠準確, 設定SQL TRACE首先要在init.ora中設定TIMED_STATISTICS, 這樣才能得到那些重要的時間狀態. 產生的trace檔案是不可讀的,所以要用TKPROF工具對其進行轉換,TKPROF有許多執行參數. 大家可以參考ORACLE手冊來瞭解具體的配置. )