載自itpub?xbxing
很好，很全面的一篇關于oracle?sql調優的文章。文章非常長，大家可一有空就看一點。
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優化器,而直接采用基于規則或者基于成本的優化器.
??2.???????訪問Table的方式
??ORACLE?采用兩種訪問表中記錄的方式:
a.???????全表掃描?
????????????全表掃描就是順序地訪問表中每條記錄.?ORACLE采用一次讀入多個數據塊(database?block)的方式優化全表掃描.
????b.???????通過ROWID訪問表
???????你可以采用基于ROWID的訪問方式情況,提高訪問表的效率,?,?ROWID包含了表中記錄的物理位置信息..ORACLE采用索引(INDEX)實現了數據和存放數據的物理位置(ROWID)之間的聯系.?通常索引提供了快速訪問ROWID的方法,因此那些基于索引列的查詢就可以得到性能上的提高.
?3.???????共享SQL語句
為了不重復解析相同的SQL語句,在第一次解析之后,?ORACLE將SQL語句存放在內存中.這塊位于系統全局區域SGA(system?global?area)的共享池(shared?buffer?pool)中的內存可以被所有的數據庫用戶共享.?因此,當你執行一個SQL語句(有時被稱為一個游標)時,如果它
和之前的執行過的語句完全相同,?ORACLE就能很快獲得已經被解析的語句以及最好的
?txfy 回復于：2003-12-02 10:04:43執行路徑.?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語句能否在這兩個用戶之間共享.
?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 回復于：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.?選擇最有效率的表名順序(只在基于規則的優化器中有效)
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎表?driving?table)將被最先處理.?在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時,?會運用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進行派序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并.
例如:??????表?TAB1?16,384?條記錄
?????????表?TAB2?1??????條記錄
?????選擇TAB2作為基礎表?(最好的方法)
?????select?count(*)?from?tab1,tab2???執行時間0.96秒
?????選擇TAB2作為基礎表?(不佳的方法)
?txfy 回復于：2003-12-02 10:06:03select?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
5.???????WHERE子句中的連接順序．
???ORACLE采用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前,?那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
?例如:
?(低效,執行時間156.3秒)
SELECT?…?
FROM?EMP?E
WHERE??SAL?>;?50000
AND????JOB?=?‘MANAGER’
AND????25?<?(SELECT?COUNT(*)?FROM?EMP
?txfy 回復于：2003-12-02 10:06:21WHERE?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’;
6.?????SELECT子句中避免使用?‘?*?‘
當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用?‘*’?是一個方便的方法.不幸的是,這是一個非常低效的方法.?實際上,ORACLE在解析的過程中,?會將’*’?依次轉換成所有的列名,?這個工作是通過查詢數據字典完成的,?這意味著將耗費更多的時間.?
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?
????????CURSOR?C1?(E_NO?NUMBER)?IS?
????????SELECT?EMP_NAME,SALARY,GRADE
?txfy 回復于：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中重新設置ARRAYSIZE參數,?可以增加每次數據庫訪問的檢索數據量?,建議值為200.
8.???????使用DECODE函數來減少處理時間
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.
例如:
???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函數高效地得到相同結果
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子句中.
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(+))
AND?NVL(‘X’,X.DUMMY)?=?NVL(‘X’,C.ROWID(+))
AND?E.EMP_NO(+)?=?1234
AND?D.DEPT_NO(+)?=?10
AND?C.CAT_TYPE(+)?=?‘RD’;
?(譯者按:?雖然采取這種方法,效率得到提高,但是程序的可讀性大大降低,所以讀者?還是要權衡之間的利弊)
10.???????刪除重復記錄
最高效的刪除重復記錄方法?(?因為使用了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?)?用來存放可以被恢復的信息.?如果你沒有COMMIT事務,ORACLE會將數據恢復到刪除之前的狀態(準確地說是恢復到執行刪除命令之前的狀況)
而當運用TRUNCATE時,?回滾段不再存放任何可被恢復的信息.當命令運行后,數據不能被恢復.因此很少的資源被調用,執行時間也會很短.
?(譯者按:?TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)
12.???????盡量多使用COMMIT
只要有可能,在程序中盡量多使用COMMIT,?這樣程序的性能得到提高,需求也會因為COMMIT所釋放的資源而減少:
?COMMIT所釋放的資源:
a.???????回滾段上用于恢復數據的信息.
b.???????被程序語句獲得的鎖
c.???????redo?log?buffer?中的空間
d.???????ORACLE為管理上述3種資源中的內部花費
?(譯者按:?在使用COMMIT時必須要注意到事務的完整性,現實中效率和事務完整性往往是魚和熊掌不可得兼)
13.???????計算記錄條數
?????和一般的觀點相反,?count(*)?比count(1)稍快?,?當然如果可以通過索引檢索,對索引列的計數仍舊是最快的.?例如?COUNT(EMPNO)
??(譯者按:?在CSDN論壇中,曾經對此有過相當熱烈的討論,?作者的觀點并不十分準確,通過實際的測試,上述三種方法并沒有顯著的性能差別)
?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子句中)
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.???????通過內部函數提高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;
通過調用下面的函數可以提高效率.
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;
?(譯者按:?經常在論壇中看到如?’能不能用一個SQL寫出….’?的貼子,?殊不知復雜的SQL往往犧牲了執行效率.?能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的)
17.???????使用表的別名(Alias)
當在SQL語句中連接多個表時,?請使用表的別名并把別名前綴于每個Column上.這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤.
??(譯者注:?Column歧義指的是由于SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬)
18.???????用EXISTS替代IN
在許多基于基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下,?使用EXISTS(或NOT?EXISTS)通常將提高查詢的效率.
?低效:
SELECT?*?
FROM?EMP?(基礎表)
WHERE?EMPNO?>;?0
AND?DEPTNO?IN?(SELECT?DEPTNO?
FROM?DEPT?
WHERE?LOC?=?‘MELB’)
????高效:
SELECT?*?
FROM?EMP?(基礎表)
WHERE?EMPNO?>;?0
AND?EXISTS?(SELECT?‘X’?
FROM?DEPT?
WHERE?DEPT.DEPTNO?=?EMP.DEPTNO
AND?LOC?=?‘MELB’)
(譯者按:?相對來說,用NOT?EXISTS替換NOT?IN?將更顯著地提高效率,下一節中將指出)
19.???????用NOT?EXISTS替代NOT?IN
在子查詢中,NOT?IN子句將執行一個內部的排序和合并.?無論在哪種情況下,NOT?IN都是最低效的?(因為它對子查詢中的表執行了一個全表遍歷).??為了避免使用NOT?IN?,我們可以把它改寫成外連接(Outer?Joins)或NOT?EXISTS.
?例如:
SELECT?…
FROM?EMP
WHERE?DEPT_NO?NOT?IN?(SELECT?DEPT_NO?
?????????????????????????FROM?DEPT?
?????????????????????????WHERE?DEPT_CAT=’A’);
為了提高效率.改寫為:
?(方法一:?高效)
SELECT?….
FROM?EMP?A,DEPT?B
WHERE?A.DEPT_NO?=?B.DEPT(+)
AND?B.DEPT_NO?IS?NULL
AND?B.DEPT_CAT(+)?=?‘A’
?(方法二:?最高效)
SELECT?….
FROM?EMP?E
WHERE?NOT?EXISTS?(SELECT?‘X’?
????????????????????FROM?DEPT?D
????????????????????WHERE?D.DEPT_NO?=?E.DEPT_NO
????????????????????AND?DEPT_CAT?=?‘A’);
20.???????用表連接替換EXISTS
?????通常來說?,?采用表連接的方式比EXISTS更有效率
??????SELECT?ENAME
??????FROM?EMP?E
??????WHERE?EXISTS?(SELECT?‘X’?
??????????????????????FROM?DEPT
??????????????????????WHERE?DEPT_NO?=?E.DEPT_NO
??????????????????????AND?DEPT_CAT?=?‘A’);
?????(更高效)
??????SELECT?ENAME
??????FROM?DEPT?D,EMP?E
??????WHERE?E.DEPT_NO?=?D.DEPT_NO
??????AND?DEPT_CAT?=?‘A’?;
??(譯者按:?在RBO的情況下,前者的執行路徑包括FILTER,后者使用NESTED?LOOP)
?21.???????用EXISTS替換DISTINCT
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT.?一般可以考慮用EXIST替換
?例如:
低效:
????SELECT?DISTINCT?DEPT_NO,DEPT_NAME
????FROM?DEPT?D,EMP?E
????WHERE?D.DEPT_NO?=?E.DEPT_NO
高效:
????SELECT?DEPT_NO,DEPT_NAME
????FROM?DEPT?D
????WHERE?EXISTS?(?SELECT?‘X’
????????????????????FROM?EMP?E
????????????????????WHERE?E.DEPT_NO?=?D.DEPT_NO);
??EXISTS?使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結果.
?22.???????識別’低效執行’的SQL語句
用下列SQL工具找出低效SQL:
?SELECT?EXECUTIONS?,?DISK_READS,?BUFFER_GETS,
????????ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)?Hit_radio,
????????ROUND(DISK_READS/EXECUTIONS,2)?Reads_per_run,
????????SQL_TEXT
FROM???V$SQLAREA
WHERE??EXECUTIONS>;0
AND?????BUFFER_GETS?>;?0?
AND?(BUFFER_GETS-DISK_READS)/BUFFER_GETS?<?0.8?
ORDER?BY?4?DESC;
?????(譯者按:?雖然目前各種關于SQL優化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法)
?txfy 回復于：2003-12-02 10:07:3923.???????使用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手冊來了解具體的配置.?)
24.???????用EXPLAIN?PLAN?分析SQL語句
?EXPLAIN?PLAN?是一個很好的分析SQL語句的工具,它甚至可以在不執行SQL的情況下分析語句.?通過分析,我們就可以知道ORACLE是怎么樣連接表,使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱.
你需要按照從里到外,從上到下的次序解讀分析的結果.?EXPLAIN?PLAN分析的結果是用縮進的格式排列的,?最內部的操作將被最先解讀,?如果兩個操作處于同一層中,帶有最小操作號的將被首先執行.
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
??????????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工具.也許喜歡圖形化界面的朋友們可以選用它們.
25.???????用索引提高效率
索引是表的一個概念部分,用來提高檢索數據的效率.?實際上,ORACLE使用了一個復雜的自平衡B-tree結構.?通常,通過索引查詢數據比全表掃描要快.?當ORACLE找出執行查詢和Update語句的最佳路徑時,?ORACLE優化器將使用索引.?同樣在聯結多個表時使用索引也可以提高效率.?另一個使用索引的好處是,它提供了主鍵(primary?key)的唯一性驗證.
除了那些LONG或LONG?RAW數據類型,?你可以索引幾乎所有的列.?通常,?在大型表中使用索引特別有效.?當然,你也會發現,?在掃描小表時,使用索引同樣能提高效率.
雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價.?索引需要空間來
存儲,也需要定期維護,?每當有記錄在表中增減或索引列被修改時,?索引本身也會被修改.?這意味著每條記錄的INSERT?,?DELETE?,?UPDATE將為此多付出4?,?5?次的磁盤I/O?.?因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.
譯者按:?
定期的重構索引是有必要的.?
ALTER?INDEX?<INDEXNAME>;?REBUILD?<TABLESPACENAME>;
26.???????索引的操作
ORACLE對索引有兩種訪問模式.
索引唯一掃描?(?INDEX?UNIQUE?SCAN)
大多數情況下,?優化器通過WHERE子句訪問INDEX.
例如:
表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’;
?
??這個SQL的執行分兩步,?LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID)?和下一步同過ROWID訪問表得到LODGING列的值.?由于LODGING$MANAGER是一個非唯一性的索引,數據庫不能對它執行索引唯一掃描.?
??由于SQL返回LODGING列,而它并不存在于LODGING$MANAGER索引中,?所以在索引范圍查詢后會執行一個通過ROWID訪問表的操作.?
??WHERE子句中,?如果索引列所對應的值的第一個字符由通配符(WILDCARD)開始,?索引將不被采用.
?SELECT?LODGING
??????FROM??LODGING
WHERE?MANAGER?LIKE?‘％HANMAN’;
?在這種情況下，ORACLE將使用全表掃描.
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表將被作為查詢中的基礎表.
28.???????多個平等的索引
當SQL語句的執行路徑可以使用分布在多個表上的多個索引時,?ORACLE會同時使用多個索引并在運行時對它們的記錄進行合并,?檢索出僅對全部索引有效的記錄.
在ORACLE選擇執行路徑時,唯一性索引的等級高于非唯一性索引.?然而這個規則只有
當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
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
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是唯一性索引,但是由于它所做的是范圍比較,?等級要比非唯一性索引的等式比較低!
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’;
32.???????避免在索引列上使用計算．
WHERE子句中，如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT?…
FROM?DEPT
WHERE?SAL?*?12?>;?25000;
高效:
SELECT?…
FROM?DEPT
WHERE?SAL??>;?25000/12;
譯者按:
這是一個非常實用的規則，請務必牢記
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會產生在和在索引列上使用函數相同的
影響.?當ORACLE”遇到”NOT,他就會停止使用索引轉而執行全表掃描.
???舉例:
???低效:?(這里,不使用索引)
???SELECT?…
???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轉化成相對應的關系操作符”?的觀點
?txfy 回復于：2003-12-02 10:08:1535.???????用>;=替代>;
如果DEPTNO上有一個索引,?
高效:
???SELECT?*
???FROM?EMP
???WHERE?DEPTNO?>;=4
???
???低效:
???SELECT?*
???FROM?EMP
???WHERE?DEPTNO?>;3
??????兩者的區別在于,?前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于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”
如果你堅持要用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)
??????????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的數據交換量的減少看出
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);
????
譯者按:
這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在ORACLE8i下，兩者的執行路徑似乎是相同的．
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;
39.???????總是使用索引的第一個列
如果索引是建立在多個列上,?只有在它的第一個列(leading?column)被where子句引用時,優化器才會選擇使用該索引.?
譯者按:
這也是一條簡單而重要的規則.?見以下實例.
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'
???
很明顯,?當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引
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
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%'
????
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?等等.
???
譯者按:
???使用hint?,?表示我們對ORACLE優化器缺省的執行路徑不滿意,需要手工修改.
這是一個很有技巧性的工作.?我建議只針對特定的,少數的SQL進行hint的優化.
對ORACLE的優化器還是要有信心(特別是CBO)
?txfy 回復于：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.???????避免改變索引列的類型.
當比較不同數據類型的數據時,?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進行隱式的類型轉換,?最好把類型轉換用顯式表現出來.?注意當字符和數值比較時,?ORACLE會優先轉換數值類型到字符類型.
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’;
下面的例子中,?‘+’是數學函數.?就象其他數學函數那樣,?停用了索引.
不使用索引:
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’;?/*將使用索引*/
46.???????連接多個掃描
如果你對一個列和一組有限的值進行比較,?優化器可能執行多次掃描并對結果進行合并連接.
舉例:
????SELECT?*?
????FROM?LODGING
????WHERE?MANAGER?IN?(‘BILL?GATES’,’KEN?MULLER’);
????優化器可能將它轉換成以下形式
????SELECT?*?
????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節似乎有矛盾之處.?
47.???????CBO下使用更具選擇性的索引
基于成本的優化器(CBO,?Cost-Based?Optimizer)對索引的選擇性進行判斷來決定索引的使用是否能提高效率.
如果索引有很高的選擇性,?那就是說對于每個不重復的索引鍵值,只對應數量很少的記錄.
比如,?表中共有100條記錄而其中有80個不重復的索引鍵值.?這個索引的選擇性就是80/100?=?0.8?.?選擇性越高,?通過索引鍵值檢索出的記錄就越少.?
如果索引的選擇性很低,?檢索數據就需要大量的索引范圍查詢操作和ROWID?訪問表的
操作.?也許會比全表掃描的效率更低.
譯者按:
下列經驗請參閱:
a.???????如果檢索數據量超過30%的表中記錄數.使用索引將沒有顯著的效率提高.?
b.???????在特定情況下,?使用索引也許會比全表掃描慢,?但這是同一個數量級上的
區別.?而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!
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也是可以考慮的,?畢竟它們的可讀性很強
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?
????譯者按:
?????本節和14節相同.?可略過.?
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性能優化沒有關系,?但是作者的功力可見一斑
51.???使用顯式的游標(CURSORs)
使用隱式的游標,將會執行兩次操作.?第一次檢索記錄,?第二次檢查TOO?MANY?ROWS?這個exception?.?而顯式游標不執行第二次操作.?
52.???優化EXPORT和IMPORT
使用較大的BUFFER(比如10MB?,?10,240,000)可以提高EXPORT和IMPORT的速度.
ORACLE將盡可能地獲取你所指定的內存大小,即使在內存不滿足,也不會報錯.這個值至少要和表中最大的列相當,否則列值會被截斷.?
譯者按:
可以肯定的是,?增加BUFFER會大大提高EXPORT?,?IMPORT的效率.?(曾經碰到過一個CASE,?增加BUFFER后,IMPORT/EXPORT快了10倍!)?
作者可能犯了一個錯誤:?“這個值至少要和表中最大的列相當,否則列值會被截斷.?“
其中最大的列也許是指最大的記錄大小.
關于EXPORT/IMPORT的優化,CSDN論壇中有一些總結性的貼子,比如關于BUFFER參數,?COMMIT參數等等,?詳情請查.
53.???分離表和索引
總是將你的表和索引建立在不同的表空間內(TABLESPACES).?決不要將不屬于ORACLE內部系統的對象存放到SYSTEM表空間里.?同時,確保數據表空間和索引表空間置于不同的硬盤上.
譯者按:
“同時,確保數據表空間和索引表空間置與不同的硬盤上.”可能改為如下更為準確?“同時,確保數據表空間和索引表空間置與不同的硬盤控制卡控制的硬盤上.”
posted on 2006-04-28 15:33
有貓相伴的日子 閱讀(782)
評論(0) 編輯 收藏 所屬分類:
pl/sql