選用適合的Oracle優化器
Oracle的優化器共有種
aRULE(基於規則)
bCOST(基於成本)
cCHOOSE(選擇性)
設置缺省的優化器可以通過對initora文件中OPTIMIZER_MODE參數的各種聲明如RULECOSTCHOOSEALL_ROWSFIRST_ROWS你當然也在SQL句級或是會話(session)級對其進行覆蓋
為了使用基於成本的優化器(CBOCostBased Optimizer)你必須經常運行analyze命令以增加數據庫中的對象統計信息(object statistics)的准確性
如果數據庫的優化器模式設置為選擇性(CHOOSE)那麼實際的優化器模式將和是否運行過analyze命令有關如果table已經被analyze過優化器模式將自動成為CBO反之數據庫將采用RULE形式的優化器
在缺省情況下Oracle采用CHOOSE優化器為了避免那些不必要的全表掃描(full table scan)你必須盡量避免使用CHOOSE優化器而直接采用基於規則或者基於成本的優化器
訪問Table的方式Oracle采用兩種訪問表中記錄的方式
a全表掃描
全表掃描就是順序地訪問表中每條記錄Oracle采用一次讀入多個數據塊(database block)的方式優化全表掃描
b 通過ROWID訪問表
你可以采用基於ROWID的訪問方式情況提高訪問表的效率ROWID包含了表中記錄的物理位置信息……Oracle采用索引(INDEX)實現了數據和存放數據的物理位置(ROWID)之間的聯系通常索引提供了快速訪問ROWID的方法因此那些基於索引列的查詢就可以得到性能上的提高
共享SQL語句
為了不重復解析相同的SQL語句在第一次解析之後Oracle將SQL語句存放在內存中這塊位於系統全局區域SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的數據庫用戶共享因此當你執行一個SQL語句(有時被稱為一個游標)時如果它和之前的執行過的語句完全相同Oracle就能很快獲得已經被解析的語句以及最好的執行路徑Oracle的這個功能大大地提高了SQL的執行性能並節省了內存的使用
可惜的是Oracle只對簡單的表提供高速緩沖(cache buffering) 這個功能並不適用於多表連接查詢
數據庫管理員必須在initora中為這個區域設置合適的參數當這個內存區域越大就可以保留更多的語句當然被共享的可能性也就越大了
當你向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(* from work_city where sdesc like NEW%能兩個用戶訪問相同的對象public synonym work_city
select asdescblocation from work_city aplant_detail b where acity_id = bcity_id不能用戶jack通過private synonym訪問plant_detail而jill是表的所有者對象不同
C兩個SQL語句中必須使用相同的名字的綁定變量(bind variables)
例如第一組的兩個SQL語句是相同的(可以共享)而第二組中的兩個語句是不同的(即使在運行時賦於不同的綁定變量相同的值)
a
select pin name from people where pin = blkpin;
select pin name from people where pin = blkpin;
b
select pin name from people where pin = blkot_ind;
select pin name from people where pin = blkov_ind;
選擇最有效率的表名順序(只在基於規則的優化器中有效)
Oracle的解析器按照從右到左的順序處理FROM子句中的表名因此FROM子句中寫在最後的表(基礎表 driving table)將被最先處理在FROM子句中包含多個表的情況下你必須選擇記錄條數最少的表作為基礎表當Oracle處理多個表時會運用排序及合並的方式連接它們首先掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序然後掃描第二個表(FROM子句中最後第二個表)最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合並
例如
表TAB 條記錄
表TAB 條記錄
選擇TAB作為基礎表(最好的方法)
select count(*) from tabtab 執行時間秒
選擇TAB作為基礎表(不佳的方法)
select count(*) from tabtab 執行時間秒
如果有個以上的表連接查詢那就需要選擇交叉表(intersection table)作為基礎表交叉表是指那個被其他表所引用的表
例如EMP表描述了LOCATION表和CATEGORY表的交集
SELECT *
FROM LOCATION L
CATEGORY C
EMP E
WHERE EEMP_NO BETWEEN AND
AND ECAT_NO = CCAT_NO
AND ELOCN = LLOCN
將比下列SQL更有效率
SELECT *
FROM EMP E
LOCATION L
CATEGORY C
WHERE ECAT_NO = CCAT_NO
AND ELOCN = LLOCN
AND EEMP_NO BETWEEN AND
WHERE子句中的連接順序
Oracle采用自下而上的順序解析WHERE子句根據這個原理表之間的連接必須寫在其他WHERE條件之前那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾
例如
(低效執行時間秒）
SELECT …
FROM EMP E
WHERE SAL >
AND JOB = MANAGER
AND < (SELECT COUNT(*) FROM EMP
WHERE MGR=EEMPNO);
(高效執行時間秒)
SELECT …
FROM EMP E
WHERE < (SELECT COUNT(*) FROM EMP
WHERE MGR=EEMPNO)
AND SAL >
AND JOB = MANAGER;
SELECT子句中避免使用 *
當你想在SELECT子句中列出所有的COLUMN時使用動態SQL列引用*是一個方便的方法不幸的是這是一個非常低效的方法實際上Oracle在解析的過程中會將*依次轉換成所有的列名這個工作是通過查詢數據字典完成的這意味著將耗費更多的時間
減少訪問數據庫的次數
當執行每條SQL語句時Oracle在內部執行了許多工作解析SQL語句估算索引的利用率綁定變量讀數據塊等等由此可見減少訪問數據庫的次數就能實際上減少Oracle的工作量
例如以下有三種方法可以檢索出雇員號等於或的職員
方法(最低效)
SELECT EMP_NAME SALARY GRADE
FROM EMP
WHERE EMP_NO = ;
SELECT EMP_NAME SALARY GRADE
FROM EMP
WHERE EMP_NO = ;
方法(次低效)
DECLARE
CURSOR C (E_NO NUMBER) IS
SELECT EMP_NAMESALARYGRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C();
FETCH C INTO … ;
OPEN C();
FETCH C INTO … ;
CLOSE C; END;
方法(高效)
SELECT AEMP_NAMEASALARYAGRADE
BEMP_NAME BSALARY BGRADE
FROM EMP AEMP B
WHERE AEMP_NO =
AND BEMP_NO = ;
注意
在SQL*PlusSQL*Forms和Pro*C中重新設置ARRAYSIZE參數可以增加每次數據庫訪問的檢索數據量建議值為
使用DECODE函數來減少處理時間
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表
例如
SELECT COUNT(*)SUM(SAL)
FROM EMP
WHERE DEPT_NO =
AND ENAME LIKESMITH%;
SELECT COUNT(*)SUM(SAL)
FROM EMP
WHERE DEPT_NO =
AND ENAME LIKESMITH%;
你可以用DECODE函數高效地得到相同結果
SELECT COUNT(DECODE(DEPT_NOXNULL)) D_COUNT
COUNT(DECODE(DEPT_NOXNULL)) D_COUNT
SUM(DECODE(DEPT_NOSALNULL)) D_SAL
SUM(DECODE(DEPT_NOSALNULL)) D_SAL
FROM EMP WHERE ENAME LIKESMITH%;
類似的DECODE函數也可以運用於GROUP BY 和ORDER BY子句中
整合簡單無關聯的數據庫訪問
如果你有幾個簡單的數據庫查詢語句你可以把它們整合到一個查詢中(即使它們之間沒有關系)
例如
SELECT NAME
FROM EMP
WHERE EMP_NO = ;
SELECT NAME
FROM DPT
WHERE DPT_NO = ;
SELECT NAME
FROM CAT
WHERE CAT_TYPE =RD;
上面的個查詢可以被合並成一個
SELECT ENAME DNAME CNAME
FROM CAT C DPT D EMP EDUAL X
WHERE NVL(XXDUMMY) = NVL(XEROWID(+))
AND NVL(XXDUMMY) = NVL(XDROWID(+))
AND NVL(XXDUMMY) = NVL(XCROWID(+))
AND EEMP_NO(+) =
AND DDEPT_NO(+) =
AND CCAT_TYPE(+) = RD;
(譯者按雖然采取這種方法效率得到提高但是程序的可讀性大大降低所以讀者還是要權衡之間的利弊)
刪除重復記錄
最高效的刪除重復記錄方法(因為使用了ROWID)
DELETE FROM EMP E
WHERE EROWID > (SELECT MIN(XROWID)
FROM EMP X
WHERE XEMP_NO = EEMP_NO);
用TRUNCATE替代DELETE
當刪除表中的記錄時在通常情況下回滾段(rollback segments)用來存放可以被恢復的信息如果你沒有COMMIT事務Oracle會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況)
而當運用TRUNCATE時回滾段不再存放任何可被恢復的信息當命令運行後數據不能被恢復因此很少的資源被調用執行時間也會很短
(譯者按TRUNCATE只在刪除全表適用TRUNCATE是DDL不是DML)
盡量多使用COMMIT
只要有可能在程序中盡量多使用COMMIT這樣程序的性能得到提高需求也會因為COMMIT所釋放的資源而減少COMMIT所釋放的資源
a回滾段上用於恢復數據的信息
b被程序語句獲得的鎖
credo log buffer中的空間
dOracle為管理上述種資源中的內部花費
(譯者按在使用COMMIT時必須要注意到事務的完整性現實中效率和事務完整性往往是魚和熊掌不可得兼)
計算記錄條數
和一般的觀點相反count(*) 比count()稍快當然如果可以通過索引檢索對索引列的計數仍舊是最快的例如 COUNT(EMPNO)
(譯者按在CSDN論壇中曾經對此有過相當熱烈的討論作者的觀點並不十分准確通過實際的測試上述三種方法並沒有顯著的性能差別)
用Where子句替換HAVING子句
避免使用HAVING子句HAVING 只會在檢索出所有記錄之後才對結果集進行過濾這個處理需要排序總計等操作如果能通過WHERE子句限制記錄的數目那就能減少這方面的開銷
例如
低效
SELECT REGIONAVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION !=SYDNEY
AND REGION !=PERTH
高效
SELECT REGIONAVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != SYDNEY
AND REGION != PERTH
GROUP BY REGION
(譯者按HAVING中的條件一般用於對一些集合函數的比較如COUNT()等等除此而外一般的條件應該寫在WHERE子句中)
減少對表的查詢
在含有子查詢的SQL語句中要特別注意減少對表的查詢
例如
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = )
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = )
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAMEDB_VER)
= ( SELECT TAB_NAMEDB_VER)
FROM TAB_COLUMNS
WHERE VERSION = )
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 = ;
高效
UPDATE EMP
SET (EMP_CAT SAL_RANGE)
= (SELECT MAX(CATEGORY) MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = ;
通過內部函數提高SQL效率
SELECT HEMPNOEENAMEHHIST_TYPETTYPE_DESCCOUNT(*)
FROM HISTORY_TYPE TEMP EEMP_HISTORY H
WHERE HEMPNO = EEMPNO
AND HHIST_TYPE = THIST_TYPE
GROUP BY HEMPNOEENAMEHHIST_TYPETTYPE_DESC;
通過調用下面的函數可以提高效率
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR
AS
TDESC VARCHAR();
CURSOR C IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C;
FETCH C INTO TDESC;
CLOSE C;
RETURN (NVL(TDESC?));
END;
FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR
AS
ENAME VARCHAR();
CURSOR C IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C;
FETCH C INTO ENAME;
CLOSE C;
RETURN (NVL(ENAME?));
END;
SELECT HEMPNOLOOKUP_EMP(HEMPNO)
HHIST_TYPELOOKUP_HIST_TYPE(HHIST_TYPE)COUNT(*)
FROM EMP_HISTORY H
GROUP BY HEMPNO HHIST_TYPE;
(譯者按經常在論壇中看到如能不能用一個SQL寫出…的貼子殊不知復雜的SQL往往犧牲了執行效率能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的)
使用表的別名(Alias)
當在SQL語句中連接多個表時請使用表的別名並把別名前綴於每個Column上這樣一來就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤
(譯者注Column歧義指的是由於SQL中不同的表具有相同的Column名當SQL語句中出現這個Column時SQL解析器無法判斷這個Column的歸屬)
用EXISTS替代IN
在許多基於基礎表的查詢中為了滿足一個條件往往需要對另一個表進行聯接在這種情況下使用EXISTS(或NOT EXISTS)通常將提高查詢的效率
低效
SELECT *
FROM EMP (基礎表)
WHERE EMPNO >
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC =MELB)
高效
SELECT *
FROM EMP (基礎表)
WHERE EMPNO >
AND EXISTS (SELECTX
FROM DEPT
WHERE DEPTDEPTNO = EMPDEPTNO
AND LOC =MELB)
(譯者按相對來說用NOT EXISTS替換NOT IN將更顯著地提高效率下一節中將指出)
用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 ADEPT B
WHERE ADEPT_NO = BDEPT(+)
AND BDEPT_NO IS NULL
AND BDEPT_CAT(+) = A
(方法二最高效)
SELECT …
FROM EMP E
WHERE NOT EXISTS (SELECT X
FROM DEPT D
WHERE DDEPT_NO = EDEPT_NO
AND DEPT_CAT = A);
用表連接替換EXISTS
通常來說采用表連接的方式比EXISTS更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT X
FROM DEPT
WHERE DEPT_NO = EDEPT_NO
AND DEPT_CAT = A);
(更高效)
SELECT ENAME
FROM DEPT DEMP E
WHERE EDEPT_NO = DDEPT_NO
AND DEPT_CAT = A ;
(譯者按在RBO的情況下前者的執行路徑包括FILTER後者使用NESTED LOOP)
用EXISTS替換DISTINCT
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時避免在SELECT子句中使用DISTINCT一般可以考慮用EXIST替換
例如
低效
SELECT DISTINCT DEPT_NODEPT_NAME
FROM DEPT DEMP E
WHERE DDEPT_NO = EDEPT_NO
高效
SELECT DEPT_NODEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT X
FROM EMP E
WHERE EDEPT_NO = DDEPT_NO);
EXISTS使查詢更為迅速因為RDBMS核心模塊將在子查詢的條件一旦滿足後立刻返回結果
識別低效執行的SQL語句
用下列SQL工具找出低效SQL
SELECT EXECUTIONS DISK_READS BUFFER_GETS
ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS) Hit_radio
ROUND(DISK_READS/EXECUTIONS) Reads_per_run
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>
AND BUFFER_GETS >
AND (BUFFER_GETSDISK_READS)/BUFFER_GETS <
ORDER BY DESC;
(譯者按雖然目前各種關於SQL優化的圖形化工具層出不窮但是寫出自己的SQL工具來解決問題始終是一個最好的方法)
使用TKPROF工具來查詢SQL性能狀態
SQL trace工具收集正在執行的SQL的性能狀態數據並記錄到一個跟蹤文件中這個跟蹤文件提供了許多有用的信息例如解析次數執行次數CPU使用時間等這些數據將可以用來優化你的系統
設置SQL TRACE在會話級別
有效
ALTER SESSION SET SQL_TRACE TRUE
設置SQL TRACE在整個數據庫有效仿你必須將SQL_TRACE參數在initora中設為TRUEUSER_DUMP_DEST參數說明了生成跟蹤文件的目錄
(譯者按這一節中作者並沒有提到TKPROF的用法對SQL TRACE的用法也不夠准確設置SQL TRACE首先要在initora中設定TIMED_STATISTICS這樣才能得到那些重要的時間狀態生成的trace文件是不可讀的所以要用TKPROF工具對其進行轉換TKPROF有許多執行參數大家可以參考Oracle手冊來了解具體的配置)
用EXPLAIN PLAN 分析SQL語句
EXPLAIN PLAN是一個很好的分析SQL語句的工具它甚至可以在不執行SQL的情況下分析語句通過分析我們就可以知道Oracle是怎麼樣連接表使用什麼方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱
你需要按照從裡到外從上到下的次序解讀分析的結果EXPLAIN PLAN分析的結果是用縮進的格式排列的 最內部的操作將被最先解讀如果兩個操作處於同一層中帶有最小操作號的將被首先執行
NESTED LOOP是少數不按照上述規則處理的操作正確的執行路徑是檢查對NESTED LOOP提供數據的操作其中操作號最小的將被最先處理
譯者按通過實踐感到還是用SQLPLUS中的SET TRACE功能比較方便
舉例
SQL> list
SELECT *
FROM dept emp
* WHERE empdeptno = deptdeptno
SQL> set autotrace traceonly /*traceonly 可以不顯示執行結果*/
SQL> /
rows selected
Execution Plan
SELECT STATEMENT ptimizer=CHOOSE
NESTED LOOPS
TABLE ACCESS (FULL) OF EMP
TABLE ACCESS (BY INDEX ROWID) OF DEPT
INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)
Statistics
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
通過以上分析可以得出實際的執行步驟是
TABLE ACCESS (FULL) OF EMP
INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF DEPT
NESTED LOOPS (JOINING AND )
注目前許多第三方的工具如TOAD和Oracle本身提供的工具如OMS的SQL Analyze都提供了極其方便的EXPLAIN PLAN工具也許喜歡圖形化界面的朋友們可以選用它們
用索引提高效率
索引是表的一個概念部分用來提高檢索數據的效率實際上Oracle使用了一個復雜的自平衡Btree結構通常通過索引查詢數據比全表掃描要快當Oracle找出執行查詢和Update語句的最佳路徑時Oracle優化器將使用索引同樣在聯結多個表時使用索引也可以提高效率另一個使用索引的好處是它提供了主鍵(primary key)的唯一性驗證
除了那些LONG或LONG RAW數據類型你可以索引幾乎所有的列通常在大型表中使用索引特別有效當然你也會發現在掃描小表時使用索引同樣能提高效率
雖然使用索引能得到查詢效率的提高但是我們也必須注意到它的代價索引需要空間來存儲也需要定期維護每當有記錄在表中增減或索引列被修改時索引本身也會被修改這意味著每條記錄的INSERTDELETEUPDATE將為此多付出次的磁盤I/O因為索引需要額外的存儲空間和處理那些不必要的索引反而會使查詢反應時間變慢
譯者按定期的重構索引是有必要的
ALTER INDEXREBUILD
索引的操作
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)
適用於兩種情況
基於一個范圍的檢索
基於非唯一性索引的檢索
例
SELECT LODGING FROM LODGING WHERE LODGING LIKE M%;
WHERE子句條件包括一系列值 Oracle將通過索引范圍查詢的方式查詢LODGING_PK 由於索引范圍查詢將返回一組值 它的效率就要比索引唯一掃描低一些
例
SELECT LODGING
FROM LODGING
WHERE MANAGER = BILL GATES;
這個SQL的執行分兩步LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID)和下一步同過ROWID訪問表得到LODGING列的值由於LODGING$MANAGER是一個非唯一性的索引數據庫不能對它執行索引唯一掃描
由於SQL返回LODGING列而它並不存在於LODGING$MANAGER索引中所以在索引范圍查詢後會執行一個通過ROWID訪問表的操作
WHERE子句中如果索引列所對應的值的第一個字符由通配符(WILDCARD)開始索引將不被采用在這種情況下Oracle將使用全表掃描
SELECT LODGING
FROM LODGING
WHERE MANAGER LIKE%HANMAN;
基礎表的選擇
基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問)根據優化器的不同SQL語句中基礎表的選擇是不一樣的
如果你使用的是CBO (COST BASED OPTIMIZER)優化器會檢查SQL語句中的每個表的物理大小索引的狀態然後選用花費最低的執行路徑
如果你用RBO (RULE BASED OPTIMIZER)並且所有的連接條件都有索引對應在這種情況下基礎表就是FROM 子句中列在最後的那個表blog
舉例
SELECT ANAME BMANAGER
FROM WORKER A
LODGING B
WHERE ALODGING = BLODING;
由於LODGING表的LODING列上有一個索引而且WORKER表中沒有相比較的索引WORKER表將被作為查詢中的基礎表
多個平等的索引
當SQL語句的執行路徑可以使用分布在多個表上的多個索引時Oracle會同時使用多個索引並在運行時對它們的記錄進行合並 檢索出僅對全部索引有效的記錄
在Oracle選擇執行路徑時唯一性索引的等級高於非唯一性索引然而這個規則只有當WHERE子句中索引列和常量比較才有效如果索引列和其他表的索引類相比較這種子句在優化器中的等級是非常低的
如果不同表中兩個想同等級的索引將被引用FROM子句中表的順序將決定哪個會被率先使用FROM子句中最後的表的索引將有最高的優先級
如果相同表中兩個想同等級的索引將被引用WHERE子句中最先被引用的索引將有最高的優先級
舉例
DEPTNO上有一個非唯一性索引EMP_CAT也有一個非唯一性索引
SELECT ENAME
FROM EMP
WHERE DEPT_NO =
AND EMP_CAT = A;
這裡DEPTNO索引將被最先檢索然後同EMP_CAT索引檢索出的記錄進行合並執行路徑如下
TABLE ACCESS BY ROWID ON EMP
ANDEQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX
等式比較和范圍比較
當WHERE子句中有索引列Oracle不能合並它們Oracle將用范圍比較
舉例
DEPTNO上有一個非唯一性索引EMP_CAT也有一個非唯一性索引
SELECT ENAME
FROM EMP
WHERE DEPTNO >
AND EMP_CAT = A;
這裡只有EMP_CAT索引被用到然後所有的記錄將逐條與DEPTNO條件進行比較執行路徑如下
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON CAT_IDX
不明確的索引等級
當Oracle無法判斷索引的等級高低差別優化器將只使用一個索引它就是在WHERE子句中被列在最前面的
舉例
DEPTNO上有一個非唯一性索引EMP_CAT也有一個非唯一性索引
SELECT ENAME
FROM EMP
WHERE DEPTNO >
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 >= and emp_type = A ;
no rows selected
Execution Plan
SELECT STATEMENT ptimizer=CHOOSE
TABLE ACCESS (BY INDEX ROWID) OF EMP
INDEX (RANGE SCAN) OF EMPTYPE (NONUNIQUE)
雖然EMPNO是唯一性索引但是由於它所做的是范圍比較等級要比非唯一性索引的等式比較低!
From:http://tw.wingwit.com/Article/program/Oracle/201311/16879.html
