Oracle 語句優化分析說明第1/2頁
2024-08-29 13:56:18
供稿：網友
1. ORACLE 的解析器按照從右到左的順序處理 FROM 子句中的表名，因此 FROM 子句中寫在最后的表(基礎表 driving table)將被最先處理。在 FROM 子句中包含多個表的情況下，你必須選擇記錄條數最少的表作為基礎表。
例如：
表 TAB1 16，384 條記錄
表 TAB2 1 條記錄
選擇 TAB2作為基礎表 (最好的方法)
select count(*) from tab1，tab2 執行時間 0.96秒
選擇 TAB2作為基礎表 (不佳的方法)
select count(*) from tab2，tab1 執行時間 26.09秒
2. ORACLE 采用自下而上的順序解析 WHERE 子句，根據這個原理，表之間的連接必須寫在其他 WHERE 條件之前， 那些可以過濾掉最大數量記錄的條件必須寫在 WHERE 子句的末尾。
例如：
(低效，執行時間 156.3秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER'
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
(高效,執行時間 10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER';
3. SELECT子句中避免使用'*'
當你想在 SELECT子句中列出所有的 COLUMN時，使用動態 SQL列引用 ‘*' 是一個方便的方法。不幸的是，這是一個非常低效的方法。 實際上，ORACLE 在解析的過程中，會將‘*' 依次轉換成所有的列名， 這個工作是通過查詢數據字典完成的， 這意味著將耗費更多的時間。
4. 使用DECODE 函數來減少處理時間, 使用 DECODE 函數可以避免重復掃描相同記錄或重復連接相同的表。
5. 刪除重復記錄
最高效的刪除重復記錄方法 ( 因為使用了 ROWID)
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);
6. 計算記錄條數
和一般的觀點相反，count(*) 比 count(1)稍快，當然如果可以通過索引檢索，對索
引列的計數仍舊是最快的。 例如 COUNT(EMPNO)
7. 用WHERE子句替換HAVING子句
避免使用 HAVING子句，HAVING 只會在檢索出所有記錄之后才對結果集進行過濾。
這個處理需要排序，總計等操作。 如果能通過 WHERE 子句限制記錄的數目，那就能減少這方面的開銷。
例如：
低效
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY'
AND REGION != ‘PERTH'
高效
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY'
AND REGION != ‘PERTH'
GROUP BY REGION
HAVING 中的條件一般用于對一些集合函數的比較，如 COUNT() 等等。除此而外，一般的條件應該寫在 WHERE 子句中。
8. 減少對表的查詢
在含有子查詢的 SQL語句中，要特別注意減少對表的查詢。
例如：
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
9. 使用表的別名ALIAS
當在 SQL語句中連接多個表時， 請使用表的別名并把別名前綴于每個 Column上。這
樣一來，就可以減少解析的時間并減少那些由 Column歧義引起的語法錯誤。
(Column歧義指的是由于 SQL中不同的表具有相同的 Column名，當 SQL語句中出現這個 Column時，SQL解析器無法判斷這個 Column的歸屬)
10. 用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')
11. 用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');
12. 用表連接替換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' ;
13. 用EXISTS替換DISTINCT
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時，避免在 SELECT 子句
中使用 DISTINCT. 一般可以考慮用 EXIST 替換
例如:
低效：
SELECT DISTINCT DEPT_NO,DEPT_N
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);
14. 避免在索引列上使用計算
WHERE 子句中，如果索引列是函數的一部分。優化器將不使用索引而使用全表掃描。
舉例：
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效：
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
：這是一個非常實用的規則，請務必牢記
