Oracle SQL 語句優化
整理：太史慈子義
作者：Black_Snail
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 4
第1章 模塊功能概要描述
在應用系統開發初期，由於開發數據庫數據比較少，對於查詢 SQL語句，複雜視圖的的
編寫等體會不出 SQL 語句各種寫法的性能優劣，但是如果將應用系統提交實際應用後，隨著
數據庫中數據的增加，系統的響應速度就成為目前系統需要解決的最主要的問題之一。系統優
化中一個很重要的方面就是 SQL語句的優化。對於海量數據，劣質 SQL語句和優質 SQL語句
之間的速度差別可以達到上百倍，可見對於一個系統不是簡單地能實現其功能就可，而是要寫
出高質量的 SQL語句，提高系統的可用性。
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 5
第2章 SQL語句編寫注意問題
下面就某些 SQL 語句的 where 子句編寫中需要注意的問題作詳細介紹。在這些 where 子句
中，即使某些列存在索引，但是由於編寫了劣質的 SQL，系統在運行該 SQL 語句時也不能使用
該索引，而同樣使用全表掃描，這就造成了響應速度的極大降低。
2.1 IS NULL 與 IS NOT NULL
任何 SQL語句，只要在 where子句中使用了 is null或 is not null，那麼 Oracle優化器就不允
許使用索引了。
2.2 聯接列
對於有聯接的列，即使最後的聯接值為一個靜態值，優化器是不會使用索引的。我們一起來
看一個例子，假定有一個職工表（ employee），對於一個職工的姓和名分成兩列存放
（FIRST_NAME和 LAST_NAME），現在要查詢一個叫比爾.克林頓（Bill Cliton）的職工。
下面是一個採用聯接查詢的 SQL語句，
select * from employss
where
first_name||''||last_name ='Beill Cliton';
上面這條語句完全可以查詢出是否有 Bill Cliton這個員工，但是這裡需要注意，系統優化器
對基於 last_name創建的索引沒有使用。 當採用下面這種 SQL語句的編寫，Oracle系統就可以採
用基於 last_name創建的索引。
Select * from employee
where
first_name ='Beill' and last_name ='Cliton';
遇到下面這種情況又如何處理呢？如果一個變量（name）中存放著 Bill Cliton這個員工的姓
名，對於這種情況我們又如何避免全程遍歷，使用索引呢？可以使用一個函數，將變量 name 中
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 6
的姓和名分開就可以了，但是有一點需要注意，這個函數是不能作用在索引列上。下面是 SQL
查詢腳本：
select * from employee
where
first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1)
and
last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1)
2.3 帶通配符（%）的 like語句
同樣以上面的例子來看這種情況。目前的需求是這樣的，要求在職工表中查詢名字中包含
cliton的人。可以採用如下的查詢 SQL語句：
select * from employee where last_name like '%cliton%';
這裡由於通配符（%）在搜尋詞首出現，所以 Oracle 系統不使用 last_name 的索引。在很多
情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當
通配符出現在字符串其他位置時，優化器就能利用索引。在下面的查詢中索引得到了使用：
select * from employee where last_name like 'c%';
2.4 Order by語句
ORDER BY 語句決定了 Oracle 如何將返回的查詢結果排序。Order by 語句對要排序的列沒
有什麼特別的限制，也可以將函數加入列中（象聯接或者附加等）。任何在 Order by 語句的非
索引項或者有計算表達式都將降低查詢速度。
仔細檢查 order by語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法
就是重寫 order by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應絕對避免在
order by子句中使用表達式。
2.5 NOT
我們在查詢時經常在 where 子句使用一些邏輯表達式，如大於、小於、等於以及不等於等
等，也可以使用 and（與）、or（或）以及 not（非）。NOT 可用來對任何邏輯運算符號取反。
下面是一個 NOT子句的例子：
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 7
... where not (status ='VALID')
如果要使用 NOT，則應在取反的短語前面加上括號，並在短語前面加上 NOT運算符。NOT
運算符包含在另外一個邏輯運算符中，這就是不等於（<>）運算符。換句話說，即使不在查詢
where子句中顯式地加入 NOT詞，NOT仍在運算符中，見下例：
... where status <>'INVALID';
再看下面這個例子：
select * from employee where salary<>3000;
對這個查詢，可以改寫為不使用 NOT：
select * from employee where salary<3000 or salary>3000;
雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢
允許 Oracle對 salary列使用索引，而第一種查詢則不能使用索引。
2.6 IN和 EXISTS
有時候會將一列和一系列值相比較。最簡單的辦法就是在 where 子句中使用子查詢。在
where子句中可以使用兩種格式的子查詢。
第一種格式是使用 IN操作符：
... where column in(select * from ... where ...);
第二種格式是使用 EXIST操作符：
... where exists (select 'X' from ...where ...);
我相信絕大多數人會使用第一種格式，因為它比較容易編寫，而實際上第二種格式要遠比第
一種格式的效率高。在 Oracle中可以幾乎將所有的 IN操作符子查詢改寫為使用 EXISTS的子查
詢。
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 8
第二種格式中，子查詢以‘select 'X'開始。運用 EXISTS 子句不管子查詢從表中抽取什麼數
據它只查看 where子句。這樣優化器就不必遍歷整個表而僅根據索引就可完成工作（這裡假定在
where語句中使用的列存在索引）。相對於 IN子句來說，EXISTS使用相連子查詢，構造起來要
比 IN子查詢困難一些。
通過使用 EXIST，Oracle 系統會首先檢查主查詢，然後運行子查詢直到它找到第一個匹配
項，這就節省了時間。Oracle 系統在執行 IN 子查詢時，首先執行子查詢，並將獲得的結果列表
存放在在一個加了索引的臨時表中。在執行子查詢之前，系統先將主查詢掛起，待子查詢執行完
畢，存放在臨時表中以後再執行主查詢。這也就是使用 EXISTS 比使用 IN 通常查詢速度快的原
因。
同時應儘可能使用 NOT EXISTS 來代替 NOT IN，儘管二者都使用了 NOT（不能使用索引
而降低速度），NOT EXISTS要比 NOT IN查詢效率更高。
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 9
第3章 SQL語句性能優化
要使 Oracle SQL語句具有最優的性能，需要從多方面進行優化，下面分別進行說明。
3.1 選用合適的 ORACLE優化器
ORACLE的優化器共有 3種:
a. RULE (基於規則) b. COST (基於成本) c. CHOOSE (選擇性)
設置缺省的優化器 ,可以通過對 init.ora 文件中 OPTIMIZER_MODE 參數的各種聲明 ,如
RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當然也在 SQL 句級或是會話(session)級對
其進行覆蓋.
為了使用基於成本的優化器(CBO, Cost-Based Optimizer) , 你必須經常運行 analyze 命令,以增
加數據庫中的對象統計信息(object statistics)的準確性.
如果數據庫的優化器模式設置為選擇性(CHOOSE),那麼實際的優化器模式將和是否運行過
analyze命令有關. 如果 table已經被 analyze過, 優化器模式將自動成為 CBO , 反之,數據庫將採用
RULE形式的優化器.
在缺省情況下,ORACLE 採用 CHOOSE 優化器, 為了避免那些不必要的全表掃描(full table
scan) , 你必須儘量避免使用 CHOOSE優化器,而直接採用基於規則或者基於成本的優化器.
3.2 訪問 Table的方式
ORACLE 採用兩種訪問表中記錄的方式:
a.全表掃描
全表掃描就是順序地訪問表中每條記錄. ORACLE 採用一次讀入多個數據塊(database block)
的方式優化全表掃描.
b.通過 ROWID訪問表
你可以採用基於 ROWID的訪問方式情況,提高訪問表的效率, , ROWID包含了表中記錄的物
理位置信息..ORACLE 採用索引(INDEX)實現了數據和存放數據的物理位置(ROWID)之間的聯繫.
通常索引提供了快速訪問 ROWID的方法,因此那些基於索引列的查詢就可以得到性能上的提高.
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 10
3.3 共享 SQL語句
為了不重複解析相同的 SQL語句,在第一次解析之後, ORACLE將 SQL語句存放在內存中.這
塊位於系統全局區域 SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的
數據庫用戶共享. 因此,當你執行一個 SQL 語句(有時被稱為一個遊標)時,如果它和之前的執行過
的語句完全相同, ORACLE 就能很快獲得已經被解析的語句以及最好的執行路徑. ORACLE 的這
個功能大大地提高了 SQL 的執行性能並節省了內存的使用.可惜的是 ORACLE 只對簡單的表提
供高速緩衝(cache buffering) ,這個功能並不適用於多表連接查詢.
數據庫管理員必須在 init.ora中為這個區域設置合適的參數,當這個內存區域越大,就可以保留
更多的語句,當然被共享的可能性也就越大了.
當你向 ORACLE 提交一個 SQL語句,ORACLE會首先在這塊內存中查找相同的語句.這裡需
要註明的是,ORACLE 對兩者採取的是一種嚴格匹配,要達成共享,SQL 語句必須完全相同(包括空
格,換行等).共享的語句必須滿足三個條件:
A.字符級的比較:
當前被執行的語句和共享池中的語句必須完全相同.
例如:
SELECT * FROM EMP;
和下列每一個都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;
B.兩個語句所指的對象必須完全相同:
例如:
用戶 對象名 如何訪問
Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
Work_city public synonym
Plant_detail table owner
考慮一下下列 SQL語句能否在這兩個用戶之間共享.
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 11
SQL 能否共享 原因
select max(sal_cap) from sal_limit; 不能 每個用戶都有一個 private synonym - sal_limit , 它們是不同的對象
select count(*) from work_city where sdesc like 'NEW%'; 能
兩個用戶訪問相同的對象public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能
用 戶 jack 通 過 private synonym 訪問 plant_detail 而jill 是表的所有者,對象不同.
C.兩個 SQL語句中必須使用相同的名字的綁定變量(bind variables)
例如：
第一組的兩個 SQL語句是相同的(可以共享),而第二組中的兩個語句是不同的(即使在運行時,
賦於不同的綁定變量相同的值)
a.
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;
b.
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;
3.4 選擇最有效率的表名順序(只在基於規則的優化器中有效)
ORACLE 的解析器按照從右到左的順序處理 FROM 子句中的表名,因此 FROM 子句中寫在
最後的表(基礎表 driving table)將被最先處理. 在 FROM 子句中包含多個表的情況下,你必須選擇
記錄條數最少的表作為基礎表.當 ORACLE 處理多個表時, 會運用排序及合併的方式連接它們.首
先,掃描第一個表(FROM 子句中最後的那個表)並對記錄進行派序,然後掃描第二個表(FROM 子句
中最後第二個表),最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合併.
例如:
表 TAB1 16,384 條記錄
表 TAB2 1 條記錄
選擇 TAB2作為基礎表 (最好的方法)
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 12
select count(*) from tab1,tab2 執行時間 0.96秒
選擇 TAB2作為基礎表 (不佳的方法)
select count(*) from tab2,tab1 執行時間 26.09秒
如果有 3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是
指那個被其他表所引用的表.
例如:
EMP表描述了 LOCATION表和 CATEGORY表的交集.
SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
將比下列 SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
3.5 WHERE子句中的連接順序
ORACLE採用自下而上的順序解析 WHERE子句,根據這個原理,表之間的連接必須寫在其他
WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
例如:
(低效,執行時間 156.3秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 13
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.6 SELECT子句中避免使用 ‘ *’
當你想在 SELECT子句中列出所有的 COLUMN時,使用動態 SQL列引用 ‘*’ 是一個方便
的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將’*’ 依次轉
換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間.
3.7 減少訪問數據庫的次數
當執行每條 SQL語句時, ORACLE在內部執行了許多工作: 解析 SQL語句, 估算索引的利用
率, 綁定變量 , 讀數據塊等等. 由此可見, 減少訪問數據庫的次數 , 就能實際上減少 ORACLE的工
作量.
例如,
以下有三種方法可以檢索出僱員號等於 0342或 0291的職員.
方法 1 (最低效)
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 291;
方法 2 (次低效)
DECLARE
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 14
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,..,.. ;
…..
OPEN C1(291);
FETCH C1 INTO …,..,.. ;
CLOSE C1;
END;
方法 3 (高效)
SELECT A.EMP_NAME , A.SALARY , A.GRADE,
B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342
OR B.EMP_NO = 291;
注意:
在 SQL*Plus , SQL*Forms和 Pro*C中重新設置 ARRAYSIZE參數, 可以增加每次數據庫訪
問的檢索數據量 ,建議值為 200
3.8 使用 DECODE函數來減少處理時間
使用 DECODE函數可以避免重複掃描相同記錄或重複連接相同的表.
例如:
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*)，SUM(SAL)
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 15
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE ‘SMITH%’;
你可以用 DECODE函數高效地得到相同結果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
類似的,DECODE函數也可以運用於 GROUP BY 和 ORDER BY子句中.
3.9 整合簡單,無關聯的數據庫訪問
如果你有幾個簡單的數據庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關
系)
例如:
SELECT NAME
FROM EMP
WHERE EMP_NO = 1234;
SELECT NAME
FROM DPT
WHERE DPT_NO = 10 ;
SELECT NAME
FROM CAT
WHERE CAT_TYPE = ‘RD’;
上面的 3個查詢可以被合併成一個:
SELECT E.NAME , D.NAME , C.NAME
FROM CAT C , DPT D , EMP E,DUAL X
WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 16
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD’;
(譯者按: 雖然採取這種方法,效率得到提高,但是程序的可讀性大大降低,所以讀者 還是要權衡
之間的利弊)
3.10 刪除重複記錄
最高效的刪除重複記錄方法 ( 因為使用了 ROWID)
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);
3.11 用 TRUNCATE替代 DELETE
當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息.
如果你沒有 COMMIT 事務,ORACLE 會將數據恢復到刪除之前的狀態(準確地說是恢復到執行刪
除命令之前的狀況).
而當運用 TRUNCATE時, 回滾段不再存放任何可被恢復的信息.當命令運行後,數據不能被恢
復.因此很少的資源被調用,執行時間也會很短.
(譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是 DDL不是 DML)
3.12 儘量多使用 COMMIT
只要有可能 ,在程序中儘量多使用 COMMIT, 這樣程序的性能得到提高 ,需求也會因為
COMMIT所釋放的資源而減少:
COMMIT所釋放的資源:
a.回滾段上用於恢復數據的信息.
b.被程序語句獲得的鎖
c.redo log buffer 中的空間
d.ORACLE為管理上述 3種資源中的內部花費
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 17
(譯者按: 在使用 COMMIT時必須要注意到事務的完整性,現實中效率和事務完整性往往是魚
和熊掌不可得兼)
3.13 計算記錄條數
和一般的觀點相反, count(*) 比 count(1)稍快 , 當然如果可以通過索引檢索,對索引列的計數仍
舊是最快的. 例如 COUNT(EMPNO)
(譯者按: 在 CSDN論壇中,曾經對此有過相當熱烈的討論, 作者的觀點並不十分準確,通過實際
的測試,上述三種方法並沒有顯著的性能差別)
3.14 用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子句中)
3.15 減少對錶的查詢
在含有子查詢的 SQL語句中,要特別注意減少對錶的查詢.
例如:
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 18
低效
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)
Update 多個 Column 例子:
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
3.16 通過內部函數提高 SQL效率.
SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 19
WHERE H.EMPNO = E.EMPNO
AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通過調用下面的函數可以提高效率.
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2
AS
TDESC VARCHAR2(30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN (NVL(TDESC,’?’));
END;
FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2
AS
ENAME VARCHAR2(30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN (NVL(ENAME,’?’));
END;
SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 20
FROM EMP_HISTORY H
GROUP BY H.EMPNO , H.HIST_TYPE;
(譯者按: 經常在論壇中看到如 ’能不能用一個 SQL寫出⋯.’ 的貼子, 殊不知複雜的 SQL往
往犧牲了執行效率. 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的)
3.17 使用表的別名(Alias)
當在 SQL 語句中連接多個表時, 請使用表的別名並把別名前綴於每個 Column 上.這樣一來,
就可以減少解析的時間並減少那些由 Column歧義引起的語法錯誤.
(譯者注: Column歧義指的是由於 SQL中不同的表具有相同的 Column名,當 SQL語句中出現
這個 Column時,SQL解析器無法判斷這個 Column的歸屬)
3.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 將更顯著地提高效率,下一節中將指出)
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 21
3.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’);
3.20 用表連接替換 EXISTS
通常來說 , 採用表連接的方式比 EXISTS更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 22
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)
3.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 核心模塊將在子查詢的條件一旦滿足後,立刻返回結
果.
3.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,
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 23
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工具來解
決問題始終是一個最好的方法)
3.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手冊來了解具體的配置. )
3.24 用 EXPLAIN PLAN 分析 SQL語句
EXPLAIN PLAN 是一個很好的分析 SQL語句的工具,它甚至可以在不執行 SQL的情況下分
析語句. 通過分析,我們就可以知道 ORACLE是怎麼樣連接表,使用什麼方式掃描表(索引掃描或全
表掃描)以及使用到的索引名稱.
你需要按照從裡到外,從上到下的次序解讀分析的結果. EXPLAIN PLAN 分析的結果是用縮
進的格式排列的, 最內部的操作將被最先解讀, 如果兩個操作處於同一層中,帶有最小操作號的將
被首先執行.
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 24
NESTED LOOP 是少數不按照上述規則處理的操作, 正確的執行路徑是檢查對 NESTED
LOOP提供數據的操作,其中操作號最小的將被最先處理.
譯者按:
通過實踐, 感到還是用 SQLPLUS中的 SET TRACE 功能比較方便.
舉例:
SQL> list
1 SELECT *
2 FROM dept, emp
3* WHERE emp.deptno = dept.deptno
SQL> set autotrace traceonly /*traceonly 可以不顯示執行結果*/
SQL> /
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
30 consistent gets
0 physical reads
0 redo size
2598 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 25
0 sorts (memory)
0 sorts (disk)
14 rows processed
通過以上分析,可以得出實際的執行步驟是:
1. TABLE ACCESS (FULL) OF 'EMP'
2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4. NESTED LOOPS (JOINING 1 AND 3)
注: 目前許多第三方的工具如 TOAD和 ORACLE本身提供的工具如 OMS的 SQL Analyze都
提供了極其方便的 EXPLAIN PLAN工具.也許喜歡圖形化界面的朋友們可以選用它們.
3.25 用索引提高效率
索引是表的一個概念部分,用來提高檢索數據的效率. 實際上,ORACLE 使用了一個複雜的自
平衡 B-tree結構. 通常,通過索引查詢數據比全表掃描要快. 當 ORACLE找出執行查詢和 Update語
句的最佳路徑時, ORACLE優化器將使用索引. 同樣在聯結多個表時使用索引也可以提高效率. 另
一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.
除了那些 LONG或 LONG RAW數據類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用
索引特別有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率.
雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,
也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條
記錄的 INSERT , DELETE , UPDATE將為此多付出 4 , 5 次的磁盤 I/O . 因為索引需要額外的存儲
空間和處理,那些不必要的索引反而會使查詢反應時間變慢.
譯者按:
定期的重構索引是有必要的.
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
3.26 索引的操作
ORACLE對索引有兩種訪問模式.
1、索引唯一掃描 ( INDEX UNIQUE SCAN)
大多數情況下, 優化器通過WHERE子句訪問 INDEX.
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 26
例如:
表 LODGING 有兩個索引 : 建立在 LODGING 列上的唯一性索引 LODGING_PK 和建立在
MANAGER列上的非唯一性索引 LODGING$MANAGER.
SELECT *
FROM LODGING
WHERE LODGING = ‘ROSE HILL’;
在內部 , 上述 SQL將被分成兩步執行, 首先 , LODGING_PK 索引將通過索引唯一掃描的方
式被訪問 , 獲得相對應的 ROWID, 通過 ROWID訪問表的方式 執行下一步檢索.
如果被檢索返回的列包括在 INDEX列中,ORACLE將不執行第二步的處理(通過 ROWID訪
問表). 因為檢索數據保存在索引中, 單單訪問索引就可以完全滿足查詢結果.
下面 SQL只需要 INDEX UNIQUE SCAN 操作.
SELECT LODGING
FROM LODGING
WHERE LODGING = ‘ROSE HILL’;
索引範圍查詢(INDEX RANGE SCAN)
適用於兩種情況:
1. 基於一個範圍的檢索
2. 基於非唯一性索引的檢索
例 1:
SELECT LODGING
FROM LODGING
WHERE LODGING LIKE ‘M%’;
WHERE子句條件包括一系列值, ORACLE將通過索引範圍查詢的方式查詢 LODGING_PK .
由於索引範圍查詢將返回一組值, 它的效率就要比索引唯一掃描低一些.
例 2:
SELECT LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES’;
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 27
這個 SQL的執行分兩步, LODGING$MANAGER的索引範圍查詢(得到所有符合條件記錄的
ROWID) 和下一步同過 ROWID訪問表得到 LODGING列的值. 由於 LODGING$MANAGER是一
個非唯一性的索引,數據庫不能對它執行索引唯一掃描.
由於 SQL返回 LODGING列,而它並不存在於 LODGING$MANAGER索引中, 所以在索引
範圍查詢後會執行一個通過 ROWID訪問表的操作.
WHERE 子句中, 如果索引列所對應的值的第一個字符由通配符(WILDCARD)開始, 索引將
不被採用.
SELECT LODGING
FROM LODGING
WHERE MANAGER LIKE ‘％HANMAN’;
在這種情況下，ORACLE將使用全表掃描.
3.27 基礎表的選擇
基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問). 根據優化器的不
同, SQL語句中基礎表的選擇是不一樣的.
如果你使用的是 CBO (COST BASED OPTIMIZER),優化器會檢查 SQL語句中的每個表的物
理大小,索引的狀態,然後選用花費最低的執行路徑.
如果你用 RBO (RULE BASED OPTIMIZER) , 並且所有的連接條件都有索引對應, 在這種情
況下, 基礎表就是 FROM 子句中列在最後的那個表.
舉例:
SELECT A.NAME , B.MANAGER
FROM WORKER A,
LODGING B
WHERE A.LODGING = B.LODING;
由於 LODGING 表的 LODING 列上有一個索引, 而且 WORKER 表中沒有相比較的索引,
WORKER表將被作為查詢中的基礎表.
3.28 多個平等的索引
當 SQL 語句的執行路徑可以使用分佈在多個表上的多個索引時, ORACLE 會同時使用多個
索引並在運行時對它們的記錄進行合併, 檢索出僅對全部索引有效的記錄.
在 ORACLE選擇執行路徑時,唯一性索引的等級高於非唯一性索引. 然而這個規則只有
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 28
當 WHERE子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較. 這種子句
在優化器中的等級是非常低的.
如果不同表中兩個想同等級的索引將被引用, FROM 子句中表的順序將決定哪個會被率先使
用. FROM子句中最後的表的索引將有最高的優先級.
如果相同表中兩個想同等級的索引將被引用, WHERE 子句中最先被引用的索引將有最高的
優先級.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME,
FROM EMP
WHERE DEPT_NO = 20
AND EMP_CAT = ‘A’;
這裡,DEPTNO 索引將被最先檢索,然後同 EMP_CAT 索引檢索出的記錄進行合併. 執行路徑
如下:
TABLE ACCESS BY ROWID ON EMP
AND-EQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX
3.29 等式比較和範圍比較
當WHERE子句中有索引列, ORACLE不能合併它們,ORACLE將用範圍比較.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT = ‘A’;
這裡只有 EMP_CAT 索引被用到,然後所有的記錄將逐條與 DEPTNO 條件進行比較. 執行路
徑如下:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON CAT_IDX
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 29
3.30 不明確的索引等級
當 ORACLE無法判斷索引的等級高低差別,優化器將只使用一個索引,它就是在 WHERE子句
中被列在最前面的.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT > ‘A’;
這裡, ORACLE只用到了 DEPT_NO索引. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
譯者按:
我們來試一下以下這種情況:
SQL> select index_name, uniqueness from user_indexes where table_name = 'EMP';
INDEX_NAME UNIQUENES
------------------------------ ---------
EMPNO UNIQUE
EMPTYPE NONUNIQUE
SQL> select * from emp where empno >= 2 and emp_type = 'A' ;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPTYPE' (NON-UNIQUE)
雖然 EMPNO是唯一性索引,但是由於它所做的是範圍比較, 等級要比非唯一性索引的等式比
較低!
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 30
3.31 強制索引失效
如果兩個或以上索引具有相同的等級,你可以強制命令 ORACLE 優化器使用其中的一個(通
過它,檢索出的記錄數量少) .
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10 /*DEPTNO上的索引將失效*/
AND EMP_TYPE || ‘’ = ‘A’ /*EMP_TYPE上的索引將失效*/
這是一種相當直接的提高查詢效率的辦法. 但是你必須謹慎考慮這種策略,一般來說,只有在你
希望單獨優化幾個 SQL時才能採用它.
這裡有一個例子關於何時採用這種策略, 假設在 EMP表的 EMP_TYPE列上有一個非唯一性
的索引而 EMP_CLASS上沒有索引.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS = ‘X’;
優化器會注意到 EMP_TYPE 上的索引並使用它. 這是目前唯一的選擇. 如果,一段時間以後,
另一個非唯一性建立在 EMP_CLASS上,優化器必須對兩個索引進行選擇,在通常情況下,優化器將
使用兩個索引並在他們的結果集合上執行排序及合併. 然而,如果其中一個索引（EMP_TYPE）接
近於唯一性而另一個索引（EMP_CLASS）上有幾千個重複的值. 排序及合併就會成為一種不必
要的負擔. 在這種情況下,你希望使優化器屏蔽掉 EMP_CLASS索引.
用下面的方案就可以解決問題.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS||’’ = ‘X’;
3.32 避免在索引列上使用計算
WHERE子句中，如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描．
舉例:
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 31
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
譯者按:
這是一個非常實用的規則，請務必牢記
3.33 自動選擇索引
如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性．
在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引．
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
這裡，只有 EMPNO上的索引是唯一性的，所以 EMPNO索引將用來檢索記錄．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
3.34 避免在索引列上使用 NOT
通常，我們要避免在索引列上使用 NOT, NOT 會產生在和在索引列上使用函數相同的影響.
當 ORACLE”遇到”NOT,他就會停止使用索引轉而執行全表掃描.
舉例:
低效: (這裡,不使用索引)
SELECT …
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 32
FROM DEPT
WHERE DEPT_CODE NOT = 0;
高效: (這裡,使用了索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE > 0;
需要注意的是,在某些時候, ORACLE優化器會自動將 NOT轉化成相對應的關係操作符.
NOT > to <=
NOT >= to <
NOT < to >=
NOT <= to >
譯者按:
在這個例子中,作者犯了一些錯誤. 例子中的低效率 SQL是不能被執行的.
我做了一些測試:
SQL> select * from emp where NOT empno > 1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPNO' (UNIQUE)
SQL> select * from emp where empno <= 1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPNO' (UNIQUE)
兩者的效率完全一樣，也許這符合作者關於” 在某些時候, ORACLE 優化器會自動將
NOT轉化成相對應的關係操作符” 的觀點．
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 33
3.35 用>=替代>
如果 DEPTNO上有一個索引,
高效:
SELECT *
FROM EMP
WHERE DEPTNO >=4
低效:
SELECT *
FROM EMP
WHERE DEPTNO >3
兩者的區別在於, 前者 DBMS 將直接跳到第一個 DEPT 等於 4 的記錄而後者將首先定位到
DEPTNO=3的記錄並且向前掃描到第一個 DEPT大於 3的記錄.
3.36 用 UNION替換 OR (適用於索引列)
通常情況下, 用 UNION替換 WHERE子句中的 OR將會起到較好的效果. 對索引列使用 OR
將造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有 column沒有被索引, 查詢效率可
能會因為你沒有選擇 OR而降低.
在下面的例子中, LOC_ID 和 REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 34
如果你堅持要用 OR, 那就需要返回記錄最少的索引列寫在最前面.
注意:
WHERE KEY1 = 10 (返回最少記錄)
OR KEY2 = 20 (返回最多記錄)
ORACLE 內部將以上轉換為
WHERE KEY1 = 10 AND
((NOT KEY1 = 10) AND KEY2 = 20)
譯者按:
下面的測試數據僅供參考: (a = 1003 返回一條記錄 , b = 1 返回 1003條記錄)
SQL> select * from unionvsor /*1st test*/
2 where a = 1003 or b = 1;
1003 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
3 2 INDEX (RANGE SCAN) OF 'UB' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
5 4 INDEX (RANGE SCAN) OF 'UA' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
144 consistent gets
0 physical reads
0 redo size
63749 bytes sent via SQL*Net to client
7751 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 35
0 sorts (disk)
1003 rows processed
SQL> select * from unionvsor /*2nd test*/
2 where b = 1 or a = 1003 ;
1003 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
3 2 INDEX (RANGE SCAN) OF 'UA' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
5 4 INDEX (RANGE SCAN) OF 'UB' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
143 consistent gets
0 physical reads
0 redo size
63749 bytes sent via SQL*Net to client
7751 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1003 rows processed
SQL> select * from unionvsor /*3rd test*/
2 where a = 1003
3 union
4 select * from unionvsor
5 where b = 1;
1003 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 36
1 0 SORT (UNIQUE)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
4 3 INDEX (RANGE SCAN) OF 'UA' (NON-UNIQUE)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
6 5 INDEX (RANGE SCAN) OF 'UB' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
63735 bytes sent via SQL*Net to client
7751 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1003 rows processed
用 UNION的效果可以從 consistent gets和 SQL*NET的數據交換量的減少看出
3.37 用 IN來替換 OR
下面的查詢可以被更有效率的語句替換:
低效:
SELECT….
FROM LOCATION
WHERE LOC_ID = 10
OR LOC_ID = 20
OR LOC_ID = 30
高效:
SELECT…
FROM LOCATION
WHERE LOC_IN IN (10,20,30);
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 37
譯者按:
這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在 ORACLE8i 下，兩者的執行
路徑似乎是相同的．
3.38 避免在索引列上使用 IS NULL和 IS NOT NULL
避免在索引中使用任何可以為空的列，ORACLE 將無法使用該索引 ．對於單列索引，如果
列包含空值，索引中將不存在此記錄. 對於複合索引，如果每個列都為空，索引中同樣不存在此
記錄. 如果至少有一個列不為空，則記錄存在於索引中．
舉例:
如果唯一性索引建立在表的 A 列和 B 列上, 並且表中存在一條記錄的 A,B 值為(123,null) ,
ORACLE將不接受下一條具有相同 A,B值（123,null）的記錄(插入). 然而如果
所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等於空. 因此你可以插入 1000
條具有相同鍵值的記錄,當然它們都是空!
因為空值不存在於索引列中,所以 WHERE 子句中對索引列進行空值比較將使 ORACLE
停用該索引.
舉例:
低效: (索引失效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0;
3.39 總是使用索引的第一個列
如果索引是建立在多個列上, 只有在它的第一個列(leading column)被 where子句引用時,優化
器才會選擇使用該索引.
譯者按:
這也是一條簡單而重要的規則. 見以下實例.
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 38
SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10));
Table created.
SQL> create index multindex on multiindexusage(inda,indb);
Index created.
SQL> set autotrace traceonly
SQL> select * from multiindexusage where inda = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'
2 1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE)
SQL> select * from multiindexusage where indb = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'
很明顯, 當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引
3.40 ORACLE內部操作
當執行查詢時,ORACLE採用了內部的操作. 下表顯示了幾種重要的內部操作.
ORACLE Clause 內部操作 ORDER BY SORT ORDER BY UNION UNION-ALL MINUS MINUS INTERSECT INTERSECT DISTINCT,MINUS,INTERSECT,UNION SORT UNIQUE MIN,MAX,COUNT SORT AGGREGATE GROUP BY SORT GROUP BY ROWNUM COUNT or COUNT STOPKEY Queries involving Joins SORT JOIN,MERGE JOIN,NESTED
LOOPS CONNECT BY CONNECT BY
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 39
3.41 用 UNION-ALL 替換 UNION ( 如果有可能的話)
當 SQL 語句需要 UNION 兩個查詢結果集合時,這兩個結果集合會以 UNION-ALL 的方式被
合併, 然後在輸出最終結果前進行排序.
如果用 UNION ALL替代 UNION, 這樣排序就不是必要了. 效率就會因此得到提高.
舉例:
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
譯者按:
需要注意的是，UNION ALL 將重複輸出兩個結果集合中相同記錄. 因此各位還是
要從業務需求分析使用 UNION ALL的可行性.
UNION 將對結果集合排序,這個操作會使用到 SORT_AREA_SIZE這塊內存. 對於這
塊內存的優化也是相當重要的. 下面的 SQL可以用來查詢排序的消耗量
Select substr(name,1,25) "Sort Area Name",
substr(value,1,15) "Value"
from v$sysstat
where name like 'sort%'
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 40
3.42 使用提示(Hints)
對於表的訪問,可以使用兩種 Hints.
FULL 和 ROWID
FULL hint 告訴 ORACLE使用全表掃描的方式訪問指定表.
例如:
SELECT /*+ FULL(EMP) */ *
FROM EMP
WHERE EMPNO = 7893;
ROWID hint 告訴 ORACLE使用 TABLE ACCESS BY ROWID的操作訪問表.
通常, 你需要採用 TABLE ACCESS BY ROWID的方式特別是當訪問大表的時候, 使用這種方
式, 你需要知道 ROIWD的值或者使用索引.
如果一個大表沒有被設定為緩存(CACHED)表而你希望它的數據在查詢結束是仍然停留在
SGA 中,你就可以使用 CACHE hint 來告訴優化器把數據保留在 SGA 中. 通常 CACHE hint 和
FULL hint 一起使用.
例如:
SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *
FROM WORK;
索引 hint 告訴 ORACLE使用基於索引的掃描方式. 你不必說明具體的索引名稱
例如:
SELECT /*+ INDEX(LODGING) */ LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES’;
在不使用 hint 的情況下, 以上的查詢應該也會使用索引,然而,如果該索引的重複值過多而你
的優化器是 CBO, 優化器就可能忽略索引. 在這種情況下, 你可以用 INDEX hint強制 ORACLE使
用該索引.
ORACLE hints 還 包 括 ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE,
USE_HASH 等等.
譯者按:
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 41
使用 hint , 表示我們對 ORACLE優化器缺省的執行路徑不滿意,需要手工修改.
這是一個很有技巧性的工作. 我建議只針對特定的,少數的 SQL進行 hint的優化.
對 ORACLE的優化器還是要有信心(特別是 CBO)
3.43 用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
非唯一性的索引(DEPT_TYPE)
低效: (索引不被使用)
SELECT DEPT_CODE
FROM DEPT
ORDER BY DEPT_TYPE
EXPLAIN PLAN:
SORT ORDER BY
TABLE ACCESS FULL
高效: (使用索引)
SELECT DEPT_CODE
FROM DEPT
WHERE DEPT_TYPE > 0
EXPLAIN PLAN:
TABLE ACCESS BY ROWID ON EMP
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 42
INDEX RANGE SCAN ON DEPT_IDX
譯者按:
ORDER BY 也能使用索引! 這的確是個容易被忽視的知識點. 我們來驗證一下:
SQL> select * from emp order by empno;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (FULL SCAN) OF 'EMPNO' (UNIQUE)
3.44 避免改變索引列的類型.
當比較不同數據類型的數據時, ORACLE自動對列進行簡單的類型轉換.
假設 EMPNO是一個數值類型的索引列.
SELECT …
FROM EMP
WHERE EMPNO = ‘123’
實際上,經過 ORACLE類型轉換, 語句轉化為:
SELECT …
FROM EMP
WHERE EMPNO = TO_NUMBER(‘123’)
幸運的是,類型轉換沒有發生在索引列上,索引的用途沒有被改變.
現在,假設 EMP_TYPE是一個字符類型的索引列.
SELECT …
FROM EMP
WHERE EMP_TYPE = 123
這個語句被 ORACLE轉換為:
SELECT …
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 43
因為內部發生的類型轉換, 這個索引將不會被用到!
譯者按:
為了避免 ORACLE對你的 SQL進行隱式的類型轉換, 最好把類型轉換用顯式表現出來. 注意
當字符和數值比較時, ORACLE會優先轉換數值類型到字符類型.
3.45 需要當心的WHERE子句
某些 SELECT 語句中的WHERE子句不使用索引. 這裡有一些例子.
在下面的例子裡, ‘!=’ 將不使用索引. 記住, 索引只能告訴你什麼存在於表中, 而不能告訴
你什麼不存在於表中.
不使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0;
使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT >0;
下面的例子中, ‘||’是字符連接函數. 就象其他函數那樣, 停用了索引.
不使用索引:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;
使用索引:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = ‘AMEX’
AND ACCOUNT_TYPE=’ A’;
下面的例子中, ‘+’是數學函數. 就象其他數學函數那樣, 停用了索引.
不使用索引:
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 44
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT + 3000 >5000;
使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT > 2000 ;
下面的例子中,相同的索引列不能互相比較,這將會啟用全表掃描.
不使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);
使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,’%’);
譯者按:
如果一定要對使用函數的列啟用索引, ORACLE 新的功能: 基於函數的索引(Function-Based
Index) 也許是一個較好的方案.
CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基於函數的索引*/
SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*將使用索引*/
3.46 連接多個掃描
如果你對一個列和一組有限的值進行比較, 優化器可能執行多次掃描並對結果進行合併連接.
舉例:
SELECT *
FROM LODGING
WHERE MANAGER IN (‘BILL GATES’,’KEN MULLER’);
優化器可能將它轉換成以下形式
SELECT *
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 45
FROM LODGING
WHERE MANAGER = ‘BILL GATES’
OR 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 (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF LODGING
INDEX (RANGE SCAN ) OF LODGING$MANAGER (NON-UNIQUE)
譯者按:
本節和第 37節似乎有矛盾之處.
3.47 CBO下使用更具選擇性的索引
基於成本的優化器(CBO, Cost-Based Optimizer)對索引的選擇性進行判斷來決定索引的使用
是否能提高效率.
如果索引有很高的選擇性, 那就是說對於每個不重複的索引鍵值,只對應數量很少的記錄.
比如, 表中共有 100條記錄而其中有 80個不重複的索引鍵值. 這個索引的選擇性就是 80/100
= 0.8 . 選擇性越高, 通過索引鍵值檢索出的記錄就越少.
如果索引的選擇性很低, 檢索數據就需要大量的索引範圍查詢操作和 ROWID 訪問表的
操作. 也許會比全表掃描的效率更低.
譯者按:
下列經驗請參閱:
a.如果檢索數據量超過 30%的表中記錄數.使用索引將沒有顯著的效率提高.
b.在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數量級上的
區別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!
Administrator
Cloudy
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Edited by Foxit ReaderCopyright(C) by Foxit Software Company,2005-2008For Evaluation Only.
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 46
3.48 避免使用耗費資源的操作
帶有 DISTINCT,UNION,MINUS,INTERSECT,ORDER 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也
是可以考慮的, 畢竟它們的可讀性很強
3.49 優化 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
譯者按:
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Administrator
Oval
Edited by Foxit ReaderCopyright(C) by Foxit Software Company,2005-2008For Evaluation Only.
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 47
本節和 14節相同. 可略過.
3.50 使用日期
當使用日期是,需要注意如果有超過 5位小數加到日期上, 這個日期會進到下一天!
例如:
1.
SELECT TO_DATE(‘01-JAN-93’+.99999)
FROM DUAL;
Returns:
’01-JAN-93 23:59:59’
2.
SELECT TO_DATE(‘01-JAN-93’+.999999)
FROM DUAL;
Returns:
’02-JAN-93 00:00:00’
譯者按:
雖然本節和 SQL性能優化沒有關係, 但是作者的功力可見一斑
3.51 使用顯式的遊標(CURSORs)
使用隱式的遊標,將會執行兩次操作. 第一次檢索記錄, 第二次檢查 TOO MANY ROWS 這個
exception . 而顯式遊標不執行第二次操作.
3.52 優化 EXPORT和 IMPORT
使用較大的 BUFFER(比如 10MB , 10,240,000)可以提高 EXPORT和 IMPORT的速度.
ORACLE將盡可能地獲取你所指定的內存大小,即使在內存不滿足,也不會報錯.這個值至少要
和表中最大的列相當,否則列值會被截斷.
譯者按:
Oracle SQL 優化
POSS軟件平臺（www.poss.cn） 48
可以肯定的是, 增加 BUFFER 會大大提高 EXPORT , IMPORT 的效率. (曾經碰到過一個
CASE, 增加 BUFFER後,IMPORT/EXPORT快了 10倍!)
作者可能犯了一個錯誤: “這個值至少要和表中最大的列相當,否則列值會被截斷. “
其中最大的列也許是指最大的記錄大小.
關於 EXPORT/IMPORT 的優化,CSDN 論壇中有一些總結性的貼子,比如關於 BUFFER 參數,
COMMIT參數等等, 詳情請查.
3.53 分離表和索引
總是將你的表和索引建立在不同的表空間內(TABLESPACES). 決不要將不屬於 ORACLE 內
部系統的對象存放到 SYSTEM表空間裡. 同時,確保數據表空間和索引表空間置於不同的硬盤上.
譯者按:
“同時,確保數據表空間和索引表空間置與不同的硬盤上.”可能改為如下更為準確 “同時,確
保數據表空間和索引表空間置與不同的硬盤控制卡控制的硬盤上.”