sql 調(diào)優(yōu)整理（轉(zhuǎn)載）?? 載自itpub xbxing
1. 選用適合的ORACLE優(yōu)化器
??? ORACLE的優(yōu)化器共有3種:
?? a.? RULE (基于規(guī)則)?? b. COST (基于成本)? c. CHOOSE (選擇性)
??? 設(shè)置缺省的優(yōu)化器,可以通過(guò)對(duì)init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當(dāng)然也在SQL句級(jí)或是會(huì)話(session)級(jí)對(duì)其進(jìn)行覆蓋.
?? 為了使用基于成本的優(yōu)化器(CBO, Cost-Based Optimizer) , 你必須經(jīng)常運(yùn)行analyze 命令,以增加數(shù)據(jù)庫(kù)中的對(duì)象統(tǒng)計(jì)信息(object statistics)的準(zhǔn)確性.
?? 如果數(shù)據(jù)庫(kù)的優(yōu)化器模式設(shè)置為選擇性(CHOOSE),那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過(guò)analyze命令有關(guān). 如果table已經(jīng)被analyze過(guò), 優(yōu)化器模式將自動(dòng)成為CBO , 反之,數(shù)據(jù)庫(kù)將采用RULE形式的優(yōu)化器.
?? 在缺省情況下,ORACLE采用CHOOSE優(yōu)化器, 為了避免那些不必要的全表掃描(full table scan) , 你必須盡量避免使用CHOOSE優(yōu)化器,而直接采用基于規(guī)則或者基于成本的優(yōu)化器.
? 2.?????? 訪問(wèn)Table的方式
? ORACLE 采用兩種訪問(wèn)表中記錄的方式:
a.?????? 全表掃描?
??????????? 全表掃描就是順序地訪問(wèn)表中每條記錄. ORACLE采用一次讀入多個(gè)數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描.
??? b.?????? 通過(guò)ROWID訪問(wèn)表
?????? 你可以采用基于ROWID的訪問(wèn)方式情況,提高訪問(wèn)表的效率, , ROWID包含了表中記錄的物理位置信息..ORACLE采用索引(INDEX)實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系. 通常索引提供了快速訪問(wèn)ROWID的方法,因此那些基于索引列的查詢就可以得到性能上的提高.
?3.?????? 共享SQL語(yǔ)句
為了不重復(fù)解析相同的SQL語(yǔ)句,在第一次解析之后, ORACLE將SQL語(yǔ)句存放在內(nèi)存中.這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer pool)中的內(nèi)存可以被所有的數(shù)據(jù)庫(kù)用戶共享. 因此,當(dāng)你執(zhí)行一個(gè)SQL語(yǔ)句(有時(shí)被稱為一個(gè)游標(biāo))時(shí),如果它
和之前的執(zhí)行過(guò)的語(yǔ)句完全相同, ORACLE就能很快獲得已經(jīng)被解析的語(yǔ)句以及最好的執(zhí)行路徑. ORACLE的這個(gè)功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用.
???? 可惜的是ORACLE只對(duì)簡(jiǎn)單的表提供高速緩沖(cache buffering) ,這個(gè)功能并不適用于多表連接查詢.
數(shù)據(jù)庫(kù)管理員必須在init.ora中為這個(gè)區(qū)域設(shè)置合適的參數(shù),當(dāng)這個(gè)內(nèi)存區(qū)域越大,就可以保留更多的語(yǔ)句,當(dāng)然被共享的可能性也就越大了.
當(dāng)你向ORACLE 提交一個(gè)SQL語(yǔ)句,ORACLE會(huì)首先在這塊內(nèi)存中查找相同的語(yǔ)句.
?這里需要注明的是,ORACLE對(duì)兩者采取的是一種嚴(yán)格匹配,要達(dá)成共享,SQL語(yǔ)句必須
完全相同(包括空格,換行等).
???? 共享的語(yǔ)句必須滿足三個(gè)條件:
?A.????? 字符級(jí)的比較:
當(dāng)前被執(zhí)行的語(yǔ)句和共享池中的語(yǔ)句必須完全相同.
????? 例如:
????????? SELECT * FROM EMP;
????? 和下列每一個(gè)都不同
????????? SELECT * from EMP;
????????? Select * From Emp;
????????? SELECT????? *???? FROM EMP;
B.????? 兩個(gè)語(yǔ)句所指的對(duì)象必須完全相同:
例如:
?? 用戶 對(duì)象名 如何訪問(wèn)
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語(yǔ)句能否在這兩個(gè)用戶之間共享.
?SQL 能否共享 原因
select max(sal_cap) from sal_limit; 不能 每個(gè)用戶都有一個(gè)private synonym - sal_limit , 它們是不同的對(duì)象
select count(*0 from work_city where sdesc like 'NEW%'; 能 兩個(gè)用戶訪問(wèn)相同的對(duì)象public synonym - work_city?
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能 用戶jack 通過(guò)private synonym訪問(wèn)plant_detail 而jill 是表的所有者,對(duì)象不同.
C.????? 兩個(gè)SQL語(yǔ)句中必須使用相同的名字的綁定變量(bind variables)
例如：第一組的兩個(gè)SQL語(yǔ)句是相同的(可以共享),而第二組中的兩個(gè)語(yǔ)句是不同的(即使在運(yùn)行時(shí),賦于不同的綁定變量相同的值)
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;
4. 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫(xiě)在最后的表(基礎(chǔ)表 driving table)將被最先處理. 在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處理多個(gè)表時(shí), 會(huì)運(yùn)用排序及合并的方式連接它們.首先,掃描第一個(gè)表(FROM子句中最后的那個(gè)表)并對(duì)記錄進(jìn)行派序,然后掃描第二個(gè)表(FROM子句中最后第二個(gè)表),最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并.
例如:????? 表 TAB1 16,384 條記錄
???????? 表 TAB2 1????? 條記錄
???? 選擇TAB2作為基礎(chǔ)表 (最好的方法)
???? select count(*) from tab1,tab2?? 執(zhí)行時(shí)間0.96秒
???? 選擇TAB2作為基礎(chǔ)表 (不佳的方法)
select count(*) from tab2,tab1?? 執(zhí)行時(shí)間26.09秒
如果有3個(gè)以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個(gè)被其他表所引用的表.
例如:?? 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
5.?????? WHERE子句中的連接順序．
?? ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原理,表之間的連接必須寫(xiě)在其他WHERE條件之前, 那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫(xiě)在WHERE子句的末尾.
?例如:
?(低效,執(zhí)行時(shí)間156.3秒)
SELECT …?
FROM EMP E
WHERE? SAL >; 50000
AND??? JOB = ‘MANAGER’
AND??? 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
?(高效,執(zhí)行時(shí)間10.6秒)
SELECT …?
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
???????????? WHERE MGR=E.EMPNO)
AND??? SAL >; 50000
AND??? JOB = ‘MANAGER’;
6.???? SELECT子句中避免使用 ‘ * ‘
當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí),使用動(dòng)態(tài)SQL列引用 ‘*’ 是一個(gè)方便的方法.不幸的是,這是一個(gè)非常低效的方法. 實(shí)際上,ORACLE在解析的過(guò)程中, 會(huì)將’*’ 依次轉(zhuǎn)換成所有的列名, 這個(gè)工作是通過(guò)查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更多的時(shí)間.?
7.???? 減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù)
當(dāng)執(zhí)行每條SQL語(yǔ)句時(shí), ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語(yǔ)句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等等. 由此可見(jiàn), 減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù) , 就能實(shí)際上減少ORACLE的工作量.
?例如,
??? 以下有三種方法可以檢索出雇員號(hào)等于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?
??????? 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
??? AND?? B.EMP_NO = 291;
?注意:
在SQL*Plus , SQL*Forms和Pro*C中重新設(shè)置ARRAYSIZE參數(shù), 可以增加每次數(shù)據(jù)庫(kù)訪問(wèn)的檢索數(shù)據(jù)量 ,建議值為200.
8.?????? 使用DECODE函數(shù)來(lái)減少處理時(shí)間
使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表.
例如:
?? SELECT COUNT(*)，SUM(SAL)
?? FROM EMP
?? WHERE DEPT_NO = 0020
?? AND ENAME LIKE ‘SMITH%’;
?? SELECT COUNT(*)，SUM(SAL)
?? FROM EMP
?? WHERE DEPT_NO = 0030
?? AND ENAME LIKE ‘SMITH%’;
你可以用DECODE函數(shù)高效地得到相同結(jié)果
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%’;
類(lèi)似的,DECODE函數(shù)也可以運(yùn)用于GROUP BY 和ORDER BY子句中.
9.?????? 整合簡(jiǎn)單,無(wú)關(guān)聯(lián)的數(shù)據(jù)庫(kù)訪問(wèn)
如果你有幾個(gè)簡(jiǎn)單的數(shù)據(jù)庫(kù)查詢語(yǔ)句,你可以把它們整合到一個(gè)查詢中(即使它們之間沒(méi)有關(guān)系)
例如:
?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個(gè)查詢可以被合并成一個(gè):
?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(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD’;
?(譯者按: 雖然采取這種方法,效率得到提高,但是程序的可讀性大大降低,所以讀者 還是要權(quán)衡之間的利弊)
10.?????? 刪除重復(fù)記錄
最高效的刪除重復(fù)記錄方法 ( 因?yàn)槭褂昧薘OWID)
DELETE FROM EMP E
WHERE E.ROWID >; (SELECT MIN(X.ROWID)?
?????????????????? FROM EMP X
?????????????????? WHERE X.EMP_NO = E.EMP_NO);
11.?????? 用TRUNCATE替代DELETE
當(dāng)刪除表中的記錄時(shí),在通常情況下, 回滾段(rollback segments ) 用來(lái)存放可以被恢復(fù)的信息. 如果你沒(méi)有COMMIT事務(wù),ORACLE會(huì)將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說(shuō)是恢復(fù)到執(zhí)行刪除命令之前的狀況)
而當(dāng)運(yùn)用TRUNCATE時(shí), 回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會(huì)很短.
?(譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)
12.?????? 盡量多使用COMMIT
只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會(huì)因?yàn)镃OMMIT所釋放的資源而減少:
?COMMIT所釋放的資源:
a.?????? 回滾段上用于恢復(fù)數(shù)據(jù)的信息.
b.?????? 被程序語(yǔ)句獲得的鎖
c.?????? redo log buffer 中的空間
d.?????? ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)
?(譯者按: 在使用COMMIT時(shí)必須要注意到事務(wù)的完整性,現(xiàn)實(shí)中效率和事務(wù)完整性往往是魚(yú)和熊掌不可得兼)
13.?????? 計(jì)算記錄條數(shù)
???? 和一般的觀點(diǎn)相反, count(*) 比count(1)稍快 , 當(dāng)然如果可以通過(guò)索引檢索,對(duì)索引列的計(jì)數(shù)仍舊是最快的. 例如 COUNT(EMPNO)
? (譯者按: 在CSDN論壇中,曾經(jīng)對(duì)此有過(guò)相當(dāng)熱烈的討論, 作者的觀點(diǎn)并不十分準(zhǔn)確,通過(guò)實(shí)際的測(cè)試,上述三種方法并沒(méi)有顯著的性能差別)
?14.?????? 用Where子句替換HAVING子句
????? 避免使用HAVING子句, HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾. 這個(gè)處理需要排序,總計(jì)等操作. 如果能通過(guò)WHERE子句限制記錄的數(shù)目,那就能減少這方面的開(kāi)銷(xiāo).
?例如:
???? 低效:
???? 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 中的條件一般用于對(duì)一些集合函數(shù)的比較,如COUNT() 等等. 除此而外,一般的條件應(yīng)該寫(xiě)在WHERE子句中)
15.?????? 減少對(duì)表的查詢
在含有子查詢的SQL語(yǔ)句中,要特別注意減少對(duì)表的查詢.
? 例如:?
???? 低效
????????? 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 多個(gè)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;
?? 16.?????? 通過(guò)內(nèi)部函數(shù)提高SQL效率.
????? SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
???? FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
???? WHERE H.EMPNO = E.EMPNO
AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通過(guò)調(diào)用下面的函數(shù)可以提高效率.
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(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO , H.HIST_TYPE;
?(譯者按: 經(jīng)常在論壇中看到如 ’能不能用一個(gè)SQL寫(xiě)出….’ 的貼子, 殊不知復(fù)雜的SQL往往犧牲了執(zhí)行效率. 能夠掌握上面的運(yùn)用函數(shù)解決問(wèn)題的方法在實(shí)際工作中是非常有意義的)
17.?????? 使用表的別名(Alias)
當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí), 請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這樣一來(lái),就可以減少解析的時(shí)間并減少那些由Column歧義引起的語(yǔ)法錯(cuò)誤.
? (譯者注: Column歧義指的是由于SQL中不同的表具有相同的Column名,當(dāng)SQL語(yǔ)句中出現(xiàn)這個(gè)Column時(shí),SQL解析器無(wú)法判斷這個(gè)Column的歸屬)
18.?????? 用EXISTS替代IN
在許多基于基礎(chǔ)表的查詢中,為了滿足一個(gè)條件,往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.
?低效:
SELECT *?
FROM EMP (基礎(chǔ)表)
WHERE EMPNO >; 0
AND DEPTNO IN (SELECT DEPTNO?
FROM DEPT?
WHERE LOC = ‘MELB’)
??? 高效:
SELECT *?
FROM EMP (基礎(chǔ)表)
WHERE EMPNO >; 0
AND EXISTS (SELECT ‘X’?
FROM DEPT?
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)
(譯者按: 相對(duì)來(lái)說(shuō),用NOT EXISTS替換NOT IN 將更顯著地提高效率,下一節(jié)中將指出)
19.?????? 用NOT EXISTS替代NOT IN
在子查詢中,NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并. 無(wú)論在哪種情況下,NOT IN都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷).? 為了避免使用NOT IN ,我們可以把它改寫(xiě)成外連接(Outer Joins)或NOT EXISTS.
?例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO?
???????????????????????? FROM DEPT?
???????????????????????? WHERE DEPT_CAT=’A’);
為了提高效率.改寫(xiě)為:
?(方法一: 高效)
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
???? 通常來(lái)說(shuō) , 采用表連接的方式比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的情況下,前者的執(zhí)行路徑包括FILTER,后者使用NESTED LOOP)
?21.?????? 用EXISTS替換DISTINCT
當(dāng)提交一個(gè)包含一對(duì)多表信息(比如部門(mén)表和雇員表)的查詢時(shí),避免在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 使查詢更為迅速,因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果.
?22.?????? 識(shí)別’低效執(zhí)行’的SQL語(yǔ)句
用下列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;
???? (譯者按: 雖然目前各種關(guān)于SQL優(yōu)化的圖形化工具層出不窮,但是寫(xiě)出自己的SQL工具來(lái)解決問(wèn)題始終是一個(gè)最好的方法)
23.?????? 使用TKPROF 工具來(lái)查詢SQL性能狀態(tài)
?SQL trace 工具收集正在執(zhí)行的SQL的性能狀態(tài)數(shù)據(jù)并記錄到一個(gè)跟蹤文件中. 這個(gè)跟蹤文件提供了許多有用的信息,例如解析次數(shù).執(zhí)行次數(shù),CPU使用時(shí)間等.這些數(shù)據(jù)將可以用來(lái)優(yōu)化你的系統(tǒng).
設(shè)置SQL TRACE在會(huì)話級(jí)別: 有效
?? ALTER SESSION SET SQL_TRACE TRUE
設(shè)置SQL TRACE 在整個(gè)數(shù)據(jù)庫(kù)有效仿, 你必須將SQL_TRACE參數(shù)在init.ora中設(shè)為T(mén)RUE, USER_DUMP_DEST參數(shù)說(shuō)明了生成跟蹤文件的目錄
? (譯者按: 這一節(jié)中,作者并沒(méi)有提到TKPROF的用法, 對(duì)SQL TRACE的用法也不夠準(zhǔn)確, 設(shè)置SQL TRACE首先要在init.ora中設(shè)定TIMED_STATISTICS, 這樣才能得到那些重要的時(shí)間狀態(tài). 生成的trace文件是不可讀的,所以要用TKPROF工具對(duì)其進(jìn)行轉(zhuǎn)換,TKPROF有許多執(zhí)行參數(shù). 大家可以參考ORACLE手冊(cè)來(lái)了解具體的配置. )
24.?????? 用EXPLAIN PLAN 分析SQL語(yǔ)句
?EXPLAIN PLAN 是一個(gè)很好的分析SQL語(yǔ)句的工具,它甚至可以在不執(zhí)行SQL的情況下分析語(yǔ)句. 通過(guò)分析,我們就可以知道ORACLE是怎么樣連接表,使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱.
你需要按照從里到外,從上到下的次序解讀分析的結(jié)果. EXPLAIN PLAN分析的結(jié)果是用縮進(jìn)的格式排列的, 最內(nèi)部的操作將被最先解讀, 如果兩個(gè)操作處于同一層中,帶有最小操作號(hào)的將被首先執(zhí)行.
NESTED LOOP是少數(shù)不按照上述規(guī)則處理的操作, 正確的執(zhí)行路徑是檢查對(duì)NESTED LOOP提供數(shù)據(jù)的操作,其中操作號(hào)最小的將被最先處理.
譯者按:?
?通過(guò)實(shí)踐, 感到還是用SQLPLUS中的SET TRACE 功能比較方便.
舉例:
?SQL>; list
? 1? SELECT *
? 2? FROM dept, emp
? 3* WHERE emp.deptno = dept.deptno
SQL>; set autotrace traceonly /*traceonly 可以不顯示執(zhí)行結(jié)果*/
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
????????? 0? sorts (memory)
????????? 0? sorts (disk)
???????? 14? rows processed
通過(guò)以上分析,可以得出實(shí)際的執(zhí)行步驟是:
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工具.也許喜歡圖形化界面的朋友們可以選用它們.
25.?????? 用索引提高效率
索引是表的一個(gè)概念部分,用來(lái)提高檢索數(shù)據(jù)的效率. 實(shí)際上,ORACLE使用了一個(gè)復(fù)雜的自平衡B-tree結(jié)構(gòu). 通常,通過(guò)索引查詢數(shù)據(jù)比全表掃描要快. 當(dāng)ORACLE找出執(zhí)行查詢和Update語(yǔ)句的最佳路徑時(shí), ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結(jié)多個(gè)表時(shí)使用索引也可以提高效率. 另一個(gè)使用索引的好處是,它提供了主鍵(primary key)的唯一性驗(yàn)證.
除了那些LONG或LONG RAW數(shù)據(jù)類(lèi)型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當(dāng)然,你也會(huì)發(fā)現(xiàn), 在掃描小表時(shí),使用索引同樣能提高效率.
雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價(jià). 索引需要空間來(lái)
存儲(chǔ),也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí), 索引本身也會(huì)被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤(pán)I/O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢.
譯者按:?
定期的重構(gòu)索引是有必要的.?
ALTER INDEX <INDEXNAME>; REBUILD <TABLESPACENAME>;
26.?????? 索引的操作
ORACLE對(duì)索引有兩種訪問(wèn)模式.
索引唯一掃描 ( INDEX UNIQUE SCAN)
大多數(shù)情況下, 優(yōu)化器通過(guò)WHERE子句訪問(wèn)INDEX.
例如:
表LODGING有兩個(gè)索引 : 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER.?
?
SELECT *?
FROM LODGING
WHERE LODGING = ‘ROSE HILL’;
?? 在內(nèi)部 , 上述SQL將被分成兩步執(zhí)行, 首先 , LODGING_PK 索引將通過(guò)索引唯一掃描的方式被訪問(wèn) , 獲得相對(duì)應(yīng)的ROWID, 通過(guò)ROWID訪問(wèn)表的方式 執(zhí)行下一步檢索.
?? 如果被檢索返回的列包括在INDEX列中,ORACLE將不執(zhí)行第二步的處理(通過(guò)ROWID訪問(wèn)表). 因?yàn)闄z索數(shù)據(jù)保存在索引中, 單單訪問(wèn)索引就可以完全滿足查詢結(jié)果.?
?? 下面SQL只需要INDEX UNIQUE SCAN 操作.
????????
??????? SELECT LODGING
??????? FROM? LODGING
WHERE LODGING = ‘ROSE HILL’;
? 索引范圍查詢(INDEX RANGE SCAN)
????? 適用于兩種情況:
1.?????? 基于一個(gè)范圍的檢索
2.?????? 基于非唯一性索引的檢索
?例1:
????? SELECT LODGING
????? FROM? LODGING
WHERE LODGING LIKE ‘M%’;
WHERE子句條件包括一系列值, ORACLE將通過(guò)索引范圍查詢的方式查詢LODGING_PK . 由于索引范圍查詢將返回一組值, 它的效率就要比索引唯一掃描
低一些.?
例2:
????? SELECT LODGING
????? FROM? LODGING
WHERE MANAGER = ‘BILL GATES’;
?
? 這個(gè)SQL的執(zhí)行分兩步, LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID) 和下一步同過(guò)ROWID訪問(wèn)表得到LODGING列的值. 由于LODGING$MANAGER是一個(gè)非唯一性的索引,數(shù)據(jù)庫(kù)不能對(duì)它執(zhí)行索引唯一掃描.?
? 由于SQL返回LODGING列,而它并不存在于LODGING$MANAGER索引中, 所以在索引范圍查詢后會(huì)執(zhí)行一個(gè)通過(guò)ROWID訪問(wèn)表的操作.?
? WHERE子句中, 如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符(WILDCARD)開(kāi)始, 索引將不被采用.
?SELECT LODGING
????? FROM? LODGING
WHERE MANAGER LIKE ‘％HANMAN’;
?在這種情況下，ORACLE將使用全表掃描.
27.?????? 基礎(chǔ)表的選擇
基礎(chǔ)表(Driving Table)是指被最先訪問(wèn)的表(通常以全表掃描的方式被訪問(wèn)). 根據(jù)優(yōu)化器的不同, SQL語(yǔ)句中基礎(chǔ)表的選擇是不一樣的.
如果你使用的是CBO (COST BASED OPTIMIZER),優(yōu)化器會(huì)檢查SQL語(yǔ)句中的每個(gè)表的物理大小,索引的狀態(tài),然后選用花費(fèi)最低的執(zhí)行路徑.
如果你用RBO (RULE BASED OPTIMIZER) , 并且所有的連接條件都有索引對(duì)應(yīng), 在這種情況下, 基礎(chǔ)表就是FROM 子句中列在最后的那個(gè)表.
舉例:
???? SELECT A.NAME , B.MANAGER
???? FROM WORKER A,?
???????????? LODGING B
???? WHERE A.LODGING = B.LODING;
由于LODGING表的LODING列上有一個(gè)索引, 而且WORKER表中沒(méi)有相比較的索引, WORKER表將被作為查詢中的基礎(chǔ)表.
28.?????? 多個(gè)平等的索引
當(dāng)SQL語(yǔ)句的執(zhí)行路徑可以使用分布在多個(gè)表上的多個(gè)索引時(shí), ORACLE會(huì)同時(shí)使用多個(gè)索引并在運(yùn)行時(shí)對(duì)它們的記錄進(jìn)行合并, 檢索出僅對(duì)全部索引有效的記錄.
在ORACLE選擇執(zhí)行路徑時(shí),唯一性索引的等級(jí)高于非唯一性索引. 然而這個(gè)規(guī)則只有
當(dāng)WHERE子句中索引列和常量比較才有效.如果索引列和其他表的索引類(lèi)相比較. 這種子句在優(yōu)化器中的等級(jí)是非常低的.
如果不同表中兩個(gè)想同等級(jí)的索引將被引用, FROM子句中表的順序?qū)Q定哪個(gè)會(huì)被率先使用. FROM子句中最后的表的索引將有最高的優(yōu)先級(jí).
如果相同表中兩個(gè)想同等級(jí)的索引將被引用, WHERE子句中最先被引用的索引將有最高的優(yōu)先級(jí).
舉例:
???? DEPTNO上有一個(gè)非唯一性索引,EMP_CAT也有一個(gè)非唯一性索引.
???? SELECT ENAME,
???? FROM EMP
???? WHERE DEPT_NO = 20
???? AND EMP_CAT = ‘A’;
這里,DEPTNO索引將被最先檢索,然后同EMP_CAT索引檢索出的記錄進(jìn)行合并. 執(zhí)行路徑如下:
TABLE ACCESS BY ROWID ON EMP
??? AND-EQUAL
??????? INDEX RANGE SCAN ON DEPT_IDX
??????? INDEX RANGE SCAN ON CAT_IDX
29.??????? 等式比較和范圍比較
???? 當(dāng)WHERE子句中有索引列, ORACLE不能合并它們,ORACLE將用范圍比較.
???? 舉例:
???? DEPTNO上有一個(gè)非唯一性索引,EMP_CAT也有一個(gè)非唯一性索引.
???? SELECT ENAME
???? FROM EMP
???? WHERE DEPTNO >; 20
???? AND EMP_CAT = ‘A’;
????
???? 這里只有EMP_CAT索引被用到,然后所有的記錄將逐條與DEPTNO條件進(jìn)行比較. 執(zhí)行路徑如下:
???? TABLE ACCESS BY ROWID ON EMP?
?????????? INDEX RANGE SCAN ON CAT_IDX
30.?????? 不明確的索引等級(jí)
當(dāng)ORACLE無(wú)法判斷索引的等級(jí)高低差別,優(yōu)化器將只使用一個(gè)索引,它就是在WHERE子句中被列在最前面的.
???? 舉例:
???? DEPTNO上有一個(gè)非唯一性索引,EMP_CAT也有一個(gè)非唯一性索引.
?????
???? SELECT ENAME
???? FROM EMP
???? WHERE DEPTNO >; 20
???? AND EMP_CAT >; ‘A’;
???? 這里, ORACLE只用到了DEPT_NO索引. 執(zhí)行路徑如下:
?????
???? TABLE ACCESS BY ROWID ON EMP
????????? INDEX RANGE SCAN ON DEPT_IDX
譯者按:
我們來(lái)試一下以下這種情況:
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是唯一性索引,但是由于它所做的是范圍比較, 等級(jí)要比非唯一性索引的等式比較低!
31.?????? 強(qiáng)制索引失效
???
如果兩個(gè)或以上索引具有相同的等級(jí),你可以強(qiáng)制命令ORACLE優(yōu)化器使用其中的一個(gè)(通過(guò)它,檢索出的記錄數(shù)量少) .
舉例:
???
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935??
AND DEPTNO + 0 = 10??? /*DEPTNO上的索引將失效*/
AND EMP_TYPE || ‘’ = ‘A’? /*EMP_TYPE上的索引將失效*/
這是一種相當(dāng)直接的提高查詢效率的辦法. 但是你必須謹(jǐn)慎考慮這種策略,一般來(lái)說(shuō),只有在你希望單獨(dú)優(yōu)化幾個(gè)SQL時(shí)才能采用它.
這里有一個(gè)例子關(guān)于何時(shí)采用這種策略,?
假設(shè)在EMP表的EMP_TYPE列上有一個(gè)非唯一性的索引而EMP_CLASS上沒(méi)有索引.?
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’?
AND EMP_CLASS = ‘X’;
優(yōu)化器會(huì)注意到EMP_TYPE上的索引并使用它. 這是目前唯一的選擇. 如果,一段時(shí)間以后, 另一個(gè)非唯一性建立在EMP_CLASS上,優(yōu)化器必須對(duì)兩個(gè)索引進(jìn)行選擇,在通常情況下,優(yōu)化器將使用兩個(gè)索引并在他們的結(jié)果集合上執(zhí)行排序及合并. 然而,如果其中一個(gè)索引（EMP_TYPE）接近于唯一性而另一個(gè)索引（EMP_CLASS）上有幾千個(gè)重復(fù)的值. 排序及合并就會(huì)成為一種不必要的負(fù)擔(dān). 在這種情況下,你希望使優(yōu)化器屏蔽掉EMP_CLASS索引.
用下面的方案就可以解決問(wèn)題.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’?
AND EMP_CLASS||’’ = ‘X’;
32.?????? 避免在索引列上使用計(jì)算．
WHERE子句中，如果索引列是函數(shù)的一部分．優(yōu)化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 >; 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL? >; 25000/12;
譯者按:
這是一個(gè)非常實(shí)用的規(guī)則，請(qǐng)務(wù)必牢記
33.?????? 自動(dòng)選擇索引
如果表中有兩個(gè)以上（包括兩個(gè)）索引，其中有一個(gè)唯一性索引，而其他是非唯一性．
在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引．
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326??
AND DEPTNO? = 20 ;
這里，只有EMPNO上的索引是唯一性的，所以EMPNO索引將用來(lái)檢索記錄．
TABLE ACCESS BY ROWID ON EMP
?????? INDEX UNIQUE SCAN ON EMP_NO_IDX
?
34.?????? 避免在索引列上使用NOT
通常， 我們要避免在索引列上使用NOT, NOT會(huì)產(chǎn)生在和在索引列上使用函數(shù)相同的
影響. 當(dāng)ORACLE”遇到”NOT,他就會(huì)停止使用索引轉(zhuǎn)而執(zhí)行全表掃描.
?? 舉例:
?? 低效: (這里,不使用索引)
?? SELECT …
?? FROM DEPT
?? WHERE DEPT_CODE NOT = 0;
???
?? 高效: (這里,使用了索引)
? SELECT …
?? FROM DEPT
?? WHERE DEPT_CODE >; 0;
?? 需要注意的是,在某些時(shí)候, ORACLE優(yōu)化器會(huì)自動(dòng)將NOT轉(zhuǎn)化成相對(duì)應(yīng)的關(guān)系操作符.
?? NOT >;? to? <=
?? NOT >;=? to? <
?? NOT <? to? >;=
?? NOT <=? to? >;
?
譯者按:
???? 在這個(gè)例子中,作者犯了一些錯(cuò)誤. 例子中的低效率SQL是不能被執(zhí)行的.
我做了一些測(cè)試:
?????
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)
????? 兩者的效率完全一樣，也許這符合作者關(guān)于” 在某些時(shí)候, ORACLE優(yōu)化器會(huì)自動(dòng)將NOT轉(zhuǎn)化成相對(duì)應(yīng)的關(guān)系操作符” 的觀點(diǎn)
35.?????? 用>;=替代>;
如果DEPTNO上有一個(gè)索引,?
高效:
?? SELECT *
?? FROM EMP
?? WHERE DEPTNO >;=4
???
?? 低效:
?? SELECT *
?? FROM EMP
?? WHERE DEPTNO >;3
????? 兩者的區(qū)別在于, 前者DBMS將直接跳到第一個(gè)DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個(gè)DEPT大于3的記錄.
36.?????? 用UNION替換OR (適用于索引列)
通常情況下, 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果. 對(duì)索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對(duì)多個(gè)索引列有效. 如果有column沒(méi)有被索引, 查詢效率可能會(huì)因?yàn)槟銢](méi)有選擇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”
如果你堅(jiān)持要用OR, 那就需要返回記錄最少的索引列寫(xiě)在最前面.
注意:
WHERE KEY1 = 10?? (返回最少記錄)
OR KEY2 = 20??????? (返回最多記錄)
ORACLE 內(nèi)部將以上轉(zhuǎn)換為
WHERE KEY1 = 10 AND
((NOT KEY1 = 10) AND KEY2 = 20)????????
譯者按:?
下面的測(cè)試數(shù)據(jù)僅供參考: (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)
????????? 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
?? 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的數(shù)據(jù)交換量的減少看出
37.?????? 用IN來(lái)替換OR
下面的查詢可以被更有效率的語(yǔ)句替換:
低效:
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);
????
譯者按:
這是一條簡(jiǎn)單易記的規(guī)則，但是實(shí)際的執(zhí)行效果還須檢驗(yàn)，在ORACLE8i下，兩者的執(zhí)行路徑似乎是相同的．
38.?????? 避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以為空的列，ORACLE將無(wú)法使用該索引 ．對(duì)于單列索引，如果列包含空值，索引中將不存在此記錄. 對(duì)于復(fù)合索引，如果每個(gè)列都為空，索引中同樣不存在此記錄. 如果至少有一個(gè)列不為空，則記錄存在于索引中．
舉例:
? 如果唯一性索引建立在表的A列和B列上, 并且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入). 然而如果
所有的索引列都為空，ORACLE將認(rèn)為整個(gè)鍵值為空而空不等于空. 因此你可以插入1000
條具有相同鍵值的記錄,當(dāng)然它們都是空!
????? 因?yàn)榭罩挡淮嬖谟谒饕兄?所以WHERE子句中對(duì)索引列進(jìn)行空值比較將使ORACLE停用該索引.
舉例:
低效: (索引失效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >;=0;
39.?????? 總是使用索引的第一個(gè)列
如果索引是建立在多個(gè)列上, 只有在它的第一個(gè)列(leading column)被where子句引用時(shí),優(yōu)化器才會(huì)選擇使用該索引.?
譯者按:
這也是一條簡(jiǎn)單而重要的規(guī)則. 見(jiàn)以下實(shí)例.
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'
???
很明顯, 當(dāng)僅引用索引的第二個(gè)列時(shí),優(yōu)化器使用了全表掃描而忽略了索引
40.?????? ORACLE內(nèi)部操作
當(dāng)執(zhí)行查詢時(shí),ORACLE采用了內(nèi)部的操作. 下表顯示了幾種重要的內(nèi)部操作.
ORACLE Clause 內(nèi)部操作
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
41.?????? 用UNION-ALL 替換UNION ( 如果有可能的話)
當(dāng)SQL語(yǔ)句需要UNION兩個(gè)查詢結(jié)果集合時(shí),這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并, 然后在輸出最終結(jié)果前進(jìn)行排序.
如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會(huì)因此得到提高.
舉例:
?? 低效：
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 將重復(fù)輸出兩個(gè)結(jié)果集合中相同記錄. 因此各位還是
要從業(yè)務(wù)需求分析使用UNION ALL的可行性.
UNION 將對(duì)結(jié)果集合排序,這個(gè)操作會(huì)使用到SORT_AREA_SIZE這塊內(nèi)存. 對(duì)于這
塊內(nèi)存的優(yōu)化也是相當(dāng)重要的. 下面的SQL可以用來(lái)查詢排序的消耗量
Select substr(name,1,25)? "Sort Area Name",
??? substr(value,1,15)?? "Value"
from v$sysstat
where name like 'sort%'
????
42.?????? 使用提示(Hints)
對(duì)于表的訪問(wèn),可以使用兩種Hints.
FULL 和 ROWID
FULL hint 告訴ORACLE使用全表掃描的方式訪問(wèn)指定表.
例如:
?? SELECT /*+ FULL(EMP) */ *
?? FROM EMP
?? WHERE EMPNO = 7893;
?? ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作訪問(wèn)表.
?? 通常, 你需要采用TABLE ACCESS BY ROWID的方式特別是當(dāng)訪問(wèn)大表的時(shí)候, 使用這種方式, 你需要知道ROIWD的值或者使用索引.
?? 如果一個(gè)大表沒(méi)有被設(shè)定為緩存(CACHED)表而你希望它的數(shù)據(jù)在查詢結(jié)束是仍然停留
在SGA中,你就可以使用CACHE hint 來(lái)告訴優(yōu)化器把數(shù)據(jù)保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用.
例如:
SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *
FROM WORK;
?? 索引hint 告訴ORACLE使用基于索引的掃描方式. 你不必說(shuō)明具體的索引名稱
例如:
?? SELECT /*+ INDEX(LODGING) */ LODGING
?? FROM LODGING
?? WHERE MANAGER = ‘BILL GATES’;
???
?? 在不使用hint的情況下, 以上的查詢應(yīng)該也會(huì)使用索引,然而,如果該索引的重復(fù)值過(guò)多而你的優(yōu)化器是CBO, 優(yōu)化器就可能忽略索引. 在這種情況下, 你可以用INDEX hint強(qiáng)制ORACLE使用該索引.
?? ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等.
???
譯者按:
?? 使用hint , 表示我們對(duì)ORACLE優(yōu)化器缺省的執(zhí)行路徑不滿意,需要手工修改.
這是一個(gè)很有技巧性的工作. 我建議只針對(duì)特定的,少數(shù)的SQL進(jìn)行hint的優(yōu)化.
對(duì)ORACLE的優(yōu)化器還是要有信心(特別是CBO)
43.?????? 用WHERE替代ORDER BY
ORDER BY 子句只在兩種嚴(yán)格的條件下使用索引.
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
???????????? INDEX RANGE SCAN ON DEPT_IDX
譯者按:
????? ORDER BY 也能使用索引! 這的確是個(gè)容易被忽視的知識(shí)點(diǎn). 我們來(lái)驗(yàn)證一下:
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)
44.?????? 避免改變索引列的類(lèi)型.
當(dāng)比較不同數(shù)據(jù)類(lèi)型的數(shù)據(jù)時(shí), ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單的類(lèi)型轉(zhuǎn)換.
假設(shè) EMPNO是一個(gè)數(shù)值類(lèi)型的索引列.
SELECT …
FROM EMP
WHERE EMPNO = ‘123’
實(shí)際上,經(jīng)過(guò)ORACLE類(lèi)型轉(zhuǎn)換, 語(yǔ)句轉(zhuǎn)化為:
SELECT …
FROM EMP
WHERE EMPNO = TO_NUMBER(‘123’)
幸運(yùn)的是,類(lèi)型轉(zhuǎn)換沒(méi)有發(fā)生在索引列上,索引的用途沒(méi)有被改變.
現(xiàn)在,假設(shè)EMP_TYPE是一個(gè)字符類(lèi)型的索引列.
SELECT …
FROM EMP
WHERE EMP_TYPE = 123
這個(gè)語(yǔ)句被ORACLE轉(zhuǎn)換為:
SELECT …
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
因?yàn)閮?nèi)部發(fā)生的類(lèi)型轉(zhuǎn)換, 這個(gè)索引將不會(huì)被用到!?
譯者按:
為了避免ORACLE對(duì)你的SQL進(jìn)行隱式的類(lèi)型轉(zhuǎn)換, 最好把類(lèi)型轉(zhuǎn)換用顯式表現(xiàn)出來(lái). 注意當(dāng)字符和數(shù)值比較時(shí), ORACLE會(huì)優(yōu)先轉(zhuǎn)換數(shù)值類(lèi)型到字符類(lèi)型.
45.?????? 需要當(dāng)心的WHERE子句
某些SELECT 語(yǔ)句中的WHERE子句不使用索引. 這里有一些例子.
在下面的例子里, ‘!=’ 將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中.?
不使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0;
使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT >;0;
下面的例子中, ‘||’是字符連接函數(shù). 就象其他函數(shù)那樣, 停用了索引.
不使用索引:
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’;
下面的例子中, ‘+’是數(shù)學(xué)函數(shù). 就象其他數(shù)學(xué)函數(shù)那樣, 停用了索引.
不使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT + 3000 >;5000;
使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT >; 2000 ;
下面的例子中,相同的索引列不能互相比較,這將會(huì)啟用全表掃描.
不使用索引:
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,’%’);
譯者按:
如果一定要對(duì)使用函數(shù)的列啟用索引, ORACLE新的功能: 基于函數(shù)的索引(Function-Based Index) 也許是一個(gè)較好的方案.
?CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基于函數(shù)的索引*/
?SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*將使用索引*/
46.?????? 連接多個(gè)掃描
如果你對(duì)一個(gè)列和一組有限的值進(jìn)行比較, 優(yōu)化器可能執(zhí)行多次掃描并對(duì)結(jié)果進(jìn)行合并連接.
舉例:
??? SELECT *?
??? FROM LODGING
??? WHERE MANAGER IN (‘BILL GATES’,’KEN MULLER’);
??? 優(yōu)化器可能將它轉(zhuǎn)換成以下形式
??? SELECT *?
??? FROM LODGING
??? WHERE MANAGER = ‘BILL GATES’
??? OR MANAGER = ’KEN MULLER’;
??? 當(dāng)選擇執(zhí)行路徑時(shí), 優(yōu)化器可能對(duì)每個(gè)條件采用LODGING$MANAGER上的索引范圍掃描. 返回的ROWID用來(lái)訪問(wèn)LODGING表的記錄 (通過(guò)TABLE ACCESS BY ROWID 的方式). 最后兩組記錄以連接(CONCATENATION)的形式被組合成一個(gè)單一的集合.
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)
譯者按:
本節(jié)和第37節(jié)似乎有矛盾之處.?
47.?????? CBO下使用更具選擇性的索引
基于成本的優(yōu)化器(CBO, Cost-Based Optimizer)對(duì)索引的選擇性進(jìn)行判斷來(lái)決定索引的使用是否能提高效率.
如果索引有很高的選擇性, 那就是說(shuō)對(duì)于每個(gè)不重復(fù)的索引鍵值,只對(duì)應(yīng)數(shù)量很少的記錄.
比如, 表中共有100條記錄而其中有80個(gè)不重復(fù)的索引鍵值. 這個(gè)索引的選擇性就是80/100 = 0.8 . 選擇性越高, 通過(guò)索引鍵值檢索出的記錄就越少.?
如果索引的選擇性很低, 檢索數(shù)據(jù)就需要大量的索引范圍查詢操作和ROWID 訪問(wèn)表的
操作. 也許會(huì)比全表掃描的效率更低.
譯者按:
下列經(jīng)驗(yàn)請(qǐng)參閱:
a.?????? 如果檢索數(shù)據(jù)量超過(guò)30%的表中記錄數(shù).使用索引將沒(méi)有顯著的效率提高.?
b.?????? 在特定情況下, 使用索引也許會(huì)比全表掃描慢, 但這是同一個(gè)數(shù)量級(jí)上的
區(qū)別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!
48.?????? 避免使用耗費(fèi)資源的操作
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語(yǔ)句會(huì)啟動(dòng)SQL引擎
執(zhí)行耗費(fèi)資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序.
例如,一個(gè)UNION查詢,其中每個(gè)查詢都帶有GROUP BY子句, GROUP BY會(huì)觸發(fā)嵌入排序(NESTED SORT) ; 這樣, 每個(gè)查詢需要執(zhí)行一次排序, 然后在執(zhí)行UNION時(shí), 又一個(gè)唯一排序(SORT UNIQUE)操作被執(zhí)行而且它只能在前面的嵌入排序結(jié)束后才能開(kāi)始執(zhí)行. 嵌入的排序的深度會(huì)大大影響查詢的效率.
通常, 帶有UNION, MINUS , INTERSECT的SQL語(yǔ)句都可以用其他方式重寫(xiě).
譯者按:
???? 如果你的數(shù)據(jù)庫(kù)的SORT_AREA_SIZE調(diào)配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強(qiáng)
49.?????? 優(yōu)化GROUP BY
提高GROUP BY 語(yǔ)句的效率, 可以通過(guò)將不需要的記錄在GROUP BY 之前過(guò)濾掉.下面兩個(gè)查詢返回相同結(jié)果但第二個(gè)明顯就快了許多.
低效:
?? 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?
??? 譯者按:
???? 本節(jié)和14節(jié)相同. 可略過(guò).?
50.?????? 使用日期?
當(dāng)使用日期是,需要注意如果有超過(guò)5位小數(shù)加到日期上, 這個(gè)日期會(huì)進(jìn)到下一天!
例如:
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’
譯者按:
??? 雖然本節(jié)和SQL性能優(yōu)化沒(méi)有關(guān)系, 但是作者的功力可見(jiàn)一斑
51.?? 使用顯式的游標(biāo)(CURSORs)
使用隱式的游標(biāo),將會(huì)執(zhí)行兩次操作. 第一次檢索記錄, 第二次檢查T(mén)OO MANY ROWS 這個(gè)exception . 而顯式游標(biāo)不執(zhí)行第二次操作.?
52.?? 優(yōu)化EXPORT和IMPORT
使用較大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度.
ORACLE將盡可能地獲取你所指定的內(nèi)存大小,即使在內(nèi)存不滿足,也不會(huì)報(bào)錯(cuò).這個(gè)值至少要和表中最大的列相當(dāng),否則列值會(huì)被截?cái)??
譯者按:
可以肯定的是, 增加BUFFER會(huì)大大提高EXPORT , IMPORT的效率. (曾經(jīng)碰到過(guò)一個(gè)CASE, 增加BUFFER后,IMPORT/EXPORT快了10倍!)?
作者可能犯了一個(gè)錯(cuò)誤: “這個(gè)值至少要和表中最大的列相當(dāng),否則列值會(huì)被截?cái)? “
其中最大的列也許是指最大的記錄大小.
關(guān)于EXPORT/IMPORT的優(yōu)化,CSDN論壇中有一些總結(jié)性的貼子,比如關(guān)于BUFFER參數(shù), COMMIT參數(shù)等等, 詳情請(qǐng)查.
53.?? 分離表和索引
總是將你的表和索引建立在不同的表空間內(nèi)(TABLESPACES). 決不要將不屬于ORACLE內(nèi)部系統(tǒng)的對(duì)象存放到SYSTEM表空間里. 同時(shí),確保數(shù)據(jù)表空間和索引表空間置于不同的硬盤(pán)上.
譯者按:
“同時(shí),確保數(shù)據(jù)表空間和索引表空間置與不同的硬盤(pán)上.”可能改為如下更為準(zhǔn)確 “同時(shí),確保數(shù)據(jù)表空間和索引表空間置與不同的硬盤(pán)控制卡控制的硬盤(pán)上.”