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