posts - 14, comments - 8, trackbacks - 0

Oracle sql 性能優(yōu)化調(diào)整
　　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，F(xiàn)IRST_ROWS . 你當(dāng)然也在SQL句級或是會話(session)級對其進(jìn)行覆蓋。
　　為了使用基于成本的優(yōu)化器(CBO， Cost-Based Optimizer) ，你必須經(jīng)常運(yùn)行analyze 命令，以增加數(shù)據(jù)庫中的對象統(tǒng)計(jì)信息(object statistics)的準(zhǔn)確性。
　　如果數(shù)據(jù)庫的優(yōu)化器模式設(shè)置為選擇性(CHOOSE)，那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過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)實(shí)現(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ù)庫用戶共享。因此，當(dāng)你執(zhí)行一個SQL語句(有時被稱為一個游標(biāo))時，如果它和之前的執(zhí)行過的語句完全相同， ORACLE就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行路徑。 ORACLE的這個功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用。
可惜的是ORACLE只對簡單的表提供高速緩沖(cache buffering) ，這個功能并不適用于多表連接查詢。數(shù)據(jù)庫管理員必須在init.ora中為這個區(qū)域設(shè)置合適的參數(shù)，當(dāng)這個內(nèi)存區(qū)域越大，就可以保留更多的語句，當(dāng)然被共享的可能性也就越大了。當(dāng)你向ORACLE 提交一個SQL語句，ORACLE會首先在這塊內(nèi)存中查找相同的語句。
　　這里需要注明的是，ORACLE對兩者采取的是一種嚴(yán)格匹配，要達(dá)成共享，SQL語句必須完全相同(包括空格，換行等)。
　　共享的語句必須滿足三個條件：
　　A. 字符級的比較：
　　當(dāng)前被執(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語句能否在這兩個用戶之間共享。
　　C. 兩個SQL語句中必須使用相同的名字的綁定變量(bind variables)
　　例如：第一組的兩個SQL語句是相同的(可以共享)，而第二組中的兩個語句是不同的(即使在運(yùn)行時，賦于不同的綁定變量相同的值)
　　a.
　　selectpin，namefrompeoplewherepin=：blk1.pin;
　　selectpin，namefrompeoplewherepin=：blk1.pin;
b.
　　selectpin，namefrompeoplewherepin=：blk1.ot_ind;
　　selectpin，namefrompeoplewherepin=：blk1.ov_ind;
　　4. 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)
　　ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理。在FROM子句中包含多個表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。當(dāng)ORACLE處理多個表時，會運(yùn)用排序及合并的方式連接它們。首先，掃描第一個表(FROM子句中最后的那個表)并對記錄進(jìn)行派序，然后掃描第二個表(FROM子句中最后第二個表)，最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進(jìn)行合并。
　　例如：
　　表 TAB1 16，384 條記錄
　　表 TAB2 1 條記錄
　　選擇TAB2作為基礎(chǔ)表 (最好的方法)
　　select count(*) from tab1，tab2 執(zhí)行時間0.96秒
　　選擇TAB2作為基礎(chǔ)表 (不佳的方法)
　　select 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
1. WHERE子句中的連接順序。
　　ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個原理，表之間的連接必須寫在其他WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。
　　例如：
　　(低效，執(zhí)行時間156.3秒)
　　SELECT…
　　FROMEMPE
　　WHERESAL>50000
　　ANDJOB=‘MANAGER’
　　AND25<(SELECTCOUNT(*)FROMEMP
　　WHEREMGR=E.EMPNO);
　　(高效,執(zhí)行時間10.6秒)
　　SELECT…
　　FROMEMPE
　　WHERE25<(SELECTCOUNT(*)FROMEMP
　　WHEREMGR=E.EMPNO)
　　ANDSAL>50000
　　ANDJOB=‘MANAGER’;
　　2. SELECT子句中避免使用 ‘ * ’
　　當(dāng)你想在SELECT子句中列出所有的COLUMN時，使用動態(tài)SQL列引用 ‘*’ 是一個方便的方法。不幸的是，這是一個非常低效的方法。實(shí)際上，ORACLE在解析的過程中，會將‘*’ 依次轉(zhuǎn)換成所有的列名，這個工作是通過查詢數(shù)據(jù)字典完成的，這意味著將耗費(fèi)更多的時間。
　　3. 減少訪問數(shù)據(jù)庫的次數(shù)
　　當(dāng)執(zhí)行每條SQL語句時， ORACLE在內(nèi)部執(zhí)行了許多工作：解析SQL語句，估算索引的利用率，綁定變量，讀數(shù)據(jù)塊等等。由此可見，減少訪問數(shù)據(jù)庫的次數(shù) ，就能實(shí)際上減少ORACLE的工作量。
　　例如，以下有三種方法可以檢索出雇員號等于0342或0291的職員。
　　方法1 (最低效)
　　SELECTEMP_NAME,SALARY,GRADE
　　FROMEMP
　　WHEREEMP_NO=342;
　　SELECTEMP_NAME,SALARY,GRADE
　　FROMEMP
　　WHEREEMP_NO=291;
　　方法2 (次低效)
　　DECLARE
　　CURSORC1(E_NONUMBER)IS
　　SELECTEMP_NAME,SALARY,GRADE
　　FROMEMP
　　WHEREEMP_NO=E_NO;
　　BEGIN
　　OPENC1(342);
　　FETCHC1INTO…,..,..;
　　OPENC1(291);
　　FETCHC1INTO…,..,..;
　　CLOSEC1;
　　END;
　　方法3 (高效)
以下是引用片段：
　　SELECTA.EMP_NAME,A.SALARY,A.GRADE,
　　B.EMP_NAME,B.SALARY,B.GRADE
　　FROMEMPA,EMPB
　　WHEREA.EMP_NO=342
　　ANDB.EMP_NO=291;
　　注意：
　　在SQL*Plus ， SQL*Forms和Pro*C中重新設(shè)置ARRAYSIZE參數(shù)，可以增加每次數(shù)據(jù)庫訪問的檢索數(shù)據(jù)量，建議值為200.
　　4. 使用DECODE函數(shù)來減少處理時間
　　使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。
　　例如：
　　SELECTCOUNT(*)，SUM(SAL)
　　FROM　EMP
　　WHEREDEPT_NO=0020
　　ANDENAMELIKE　‘SMITH%’;
　　SELECTCOUNT(*)，SUM(SAL)
　　FROM　EMP
　　WHEREDEPT_NO=0030
　　ANDENAMELIKE　‘SMITH%’;
　　你可以用DECODE函數(shù)高效地得到相同結(jié)果
　　SELECTCOUNT(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
　　FROMEMPWHEREENAMELIKE‘SMITH%’;
　　類似的，DECODE函數(shù)也可以運(yùn)用于GROUP BY 和ORDER BY子句中。
　　5. 整合簡單，無關(guān)聯(lián)的數(shù)據(jù)庫訪問
　　如果你有幾個簡單的數(shù)據(jù)庫查詢語句，你可以把它們整合到一個查詢中(即使它們之間沒有關(guān)系)
　　例如：
　　SELECTNAME
　　FROMEMP
　　WHEREEMP_NO=1234;
　　SELECTNAME
　　FROMDPT
　　WHEREDPT_NO=10;
　　SELECTNAME
　　FROMCAT
　　WHERECAT_TYPE=‘RD’;
　　上面的3個查詢可以被合并成一個：
　　SELECTE.NAME,D.NAME,C.NAME
　　FROMCATC,DPTD,EMPE,DUALX
　　WHERENVL(‘X’,X.DUMMY)=NVL(‘X’,E.ROWID(+))
　　ANDNVL(‘X’,X.DUMMY)=NVL(‘X’,D.ROWID(+))
　　ANDNVL(‘X’,X.DUMMY)=NVL(‘X’,C.ROWID(+))
　　ANDE.EMP_NO(+)=1234
　　ANDD.DEPT_NO(+)=10
　　ANDC.CAT_TYPE(+)=‘RD’;

1. 刪除重復(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);
　　2. 用TRUNCATE替代DELETE
　　當(dāng)刪除表中的記錄時，在通常情況下，回滾段(rollback segments ) 用來存放可以被恢復(fù)的信息。如果你沒有COMMIT事務(wù)，ORACLE會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況)
　　而當(dāng)運(yùn)用TRUNCATE時，回滾段不再存放任何可被恢復(fù)的信息。當(dāng)命令運(yùn)行后，數(shù)據(jù)不能被恢復(fù)。因此很少的資源被調(diào)用，執(zhí)行時間也會很短。
　　(譯者按： TRUNCATE只在刪除全表適用，TRUNCATE是DDL不是DML)
　　３. 盡量多使用COMMIT
　　只要有可能，在程序中盡量多使用COMMIT，這樣程序的性能得到提高，需求也會因?yàn)镃OMMIT所釋放的資源而減少：COMMIT所釋放的資源：
　　a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息。
　　b. 被程序語句獲得的鎖
　　c. redo log buffer 中的空間
　　d. ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)
　　(譯者按：在使用COMMIT時必須要注意到事務(wù)的完整性，現(xiàn)實(shí)中效率和事務(wù)完整性往往是魚和熊掌不可得兼)
　　４. 計(jì)算記錄條數(shù)
　　和一般的觀點(diǎn)相反， count(*) 比count(1)稍快，當(dāng)然如果可以通過索引檢索，對索引列的計(jì)數(shù)仍舊是最快的。例如 COUNT(EMPNO)
　　(譯者按：在CSDN論壇中，曾經(jīng)對此有過相當(dāng)熱烈的討論，作者的觀點(diǎn)并不十分準(zhǔn)確，通過實(shí)際的測試，上述三種方法并沒有顯著的性能差別)
　　５. 用Where子句替換HAVING子句
　　避免使用HAVING子句， HAVING 只會在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾。這個處理需要排序，總計(jì)等操作。如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。
　　例如：
　　低效
　　SELECTREGION，AVG(LOG_SIZE)
　　FROMLOCATION
　　GROUPBYREGION
　　HAVINGREGIONREGION!=‘SYDNEY’
　　ANDREGION!=‘PERTH’
　　高效
　　SELECTREGION，AVG(LOG_SIZE)
　　FROMLOCATION
　　WHEREREGIONREGION!=‘SYDNEY’
　　ANDREGION!=‘PERTH’
　　GROUPBYREGION
　　(譯者按： HAVING 中的條件一般用于對一些集合函數(shù)的比較，如COUNT() 等等。除此而外，一般的條件應(yīng)該寫在WHERE子句中)
　　6. 減少對表的查詢
　　在含有子查詢的SQL語句中，要特別注意減少對表的查詢。
　　例如：
　　低效
　　SELECTTAB_NAME
　　FROMTABLES
　　WHERETAB_NAME=(SELECTTAB_NAME
　　FROMTAB_COLUMNS
　　WHEREVERSION=604)
　　AND　DB_VER=(SELECTDB_VER
　　FROMTAB_COLUMNS
　　WHEREVERSION=604)
　　高效
　　SELECTTAB_NAME
　　FROMTABLES
　　WHERE(TAB_NAME,DB_VER)
　　=(SELECTTAB_NAME,DB_VER)
　　FROMTAB_COLUMNS
　　WHEREVERSION=604)
　　Update 多個Column 例子：
　　低效：
　　UPDATEEMP
　　SETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES)

1. 使用表的別名(Alias)
　　當(dāng)在SQL語句中連接多個表時，請使用表的別名并把別名前綴于每個Column上。這樣一來，就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。
　　(Column歧義指的是由于SQL中不同的表具有相同的Column名，當(dāng)SQL語句中出現(xiàn)這個Column時，SQL解析器無法判斷這個Column的歸屬)
　　2. 用EXISTS替代IN
　　在許多基于基礎(chǔ)表的查詢中，為了滿足一個條件，往往需要對另一個表進(jìn)行聯(lián)接。在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。
　　低效：
　　SELECT*
　　FROMEMP(基礎(chǔ)表)
　　WHEREEMPNO>0
　　ANDDEPTNOIN(SELECTDEPTNO
　　FROMDEPT
　　WHERELOC=‘MELB’)
　　高效：
　　SELECT*
　　FROMEMP(基礎(chǔ)表)
　　WHEREEMPNO>0
　　ANDEXISTS(SELECT‘X’
　　FROMDEPT
　　WHEREDEPT.DEPTNO=EMP.DEPTNO
　　ANDLOC=‘MELB’)
　　(相對來說，用NOT EXISTS替換NOT IN 將更顯著地提高效率，下面將指出)
　　3. 用NOT EXISTS替代NOT IN
　　在子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并。無論在哪種情況下，NOT IN都是最低效的 (因?yàn)樗鼘ψ硬樵冎械谋韴?zhí)行了一個全表遍歷)。為了避免使用NOT IN ，我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS.
　　例如：
　　SELECT…
　　FROMEMP
　　WHEREDEPT_NONOTIN(SELECTDEPT_NO
　　FROMDEPT
　　WHEREDEPT_CAT=’A’);
　　為了提高效率。改寫為：
　　(方法一：高效)
　　SELECT….
　　FROMEMPA,DEPTB
　　WHEREA.DEPT_NO=B.DEPT(+)
　　ANDB.DEPT_NOISNULL
　　ANDB.DEPT_CAT(+)=‘A’

1. 用EXPLAIN PLAN 分析SQL語句

　　EXPLAIN PLAN 是一個很好的分析SQL語句的工具，它甚至可以在不執(zhí)行SQL的情況下分析語句。通過分析，我們就可以知道ORACLE是怎么樣連接表，使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱。

　　你需要按照從里到外，從上到下的次序解讀分析的結(jié)果。 EXPLAIN PLAN分析的結(jié)果是用縮進(jìn)的格式排列的，最內(nèi)部的操作將被最先解讀，如果兩個操作處于同一層中，帶有最小操作號的將被首先執(zhí)行。

　　NESTED LOOP是少數(shù)不按照上述規(guī)則處理的操作，正確的執(zhí)行路徑是檢查對NESTED LOOP提供數(shù)據(jù)的操作，其中操作號最小的將被最先處理。

　　通過實(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

通過以上分析，可以得出實(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工具。也許喜歡圖形化界面的朋友們可以選用它們。

　　2. 用索引提高效率

　　索引是表的一個概念部分，用來提高檢索數(shù)據(jù)的效率。實(shí)際上，ORACLE使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu)。通常，通過索引查詢數(shù)據(jù)比全表掃描要快。當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時， ORACLE優(yōu)化器將使用索引。同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率。另一個使用索引的好處是，它提供了主鍵(primary key)的唯一性驗(yàn)證。

　　除了那些LONG或LONG RAW數(shù)據(jù)類型，你可以索引幾乎所有的列。通常，在大型表中使用索引特別有效。當(dāng)然，你也會發(fā)現(xiàn)，在掃描小表時，使用索引同樣能提高效率。

　　雖然使用索引能得到查詢效率的提高，但是我們也必須注意到它的代價。索引需要空間來存儲，也需要定期維護(hù)，每當(dāng)有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味著每條記錄的INSERT ， DELETE ， UPDATE將為此多付出4 ， 5 次的磁盤I/O . 因?yàn)樗饕枰~外的存儲空間和處理，那些不必要的索引反而會使查詢反應(yīng)時間變慢。

　　定期的重構(gòu)索引是有必要的。

　　ALTER INDEX REBUILD

　　3. 索引的操作

　　ORACLE對索引有兩種訪問模式。

　　索引唯一掃描 ( INDEX UNIQUE SCAN)

　　大多數(shù)情況下，優(yōu)化器通過WHERE子句訪問INDEX.

表LODGING有兩個索引：建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER.

　　SELECT*
　　FROMLODGING
　　WHERELODGING=‘ROSEHILL’;

　　在內(nèi)部，上述SQL將被分成兩步執(zhí)行，首先， LODGING_PK 索引將通過索引唯一掃描的方式被訪問，獲得相對應(yīng)的ROWID，通過ROWID訪問表的方式執(zhí)行下一步檢索。

　　如果被檢索返回的列包括在INDEX列中，ORACLE將不執(zhí)行第二步的處理(通過ROWID訪問表)。因?yàn)闄z索數(shù)據(jù)保存在索引中，單單訪問索引就可以完全滿足查詢結(jié)果。

　　下面SQL只需要INDEX UNIQUE SCAN 操作。

　　SELECTLODGING
　　FROMLODGING
　　WHERELODGING=‘ROSEHILL’;

　　索引范圍查詢(INDEX RANGE SCAN)

　　適用于兩種情況：

　　1. 基于一個范圍的檢索

　　2. 基于非唯一性索引的檢索

　　例1：

　　SELECT LODGING FROM LODGING WHERE LODGING LIKE ‘M%’;

　　WHERE子句條件包括一系列值， ORACLE將通過索引范圍查詢的方式查詢LODGING_PK . 由于索引范圍查詢將返回一組值，它的效率就要比索引唯一掃描低一些。

　　例2：

　　SELECTLODGING
　　FROMLODGING
　　WHEREMANAGER=‘BILLGATES’;

　　這個SQL的執(zhí)行分兩步， LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID) 和下一步同過ROWID訪問表得到LODGING列的值。由于LODGING$MANAGER是一個非唯一性的索引，數(shù)據(jù)庫不能對它執(zhí)行索引唯一掃描。

　　由于SQL返回LODGING列，而它并不存在于LODGING$MANAGER索引中，所以在索引范圍查詢后會執(zhí)行一個通過ROWID訪問表的操作。

　　WHERE子句中，如果索引列所對應(yīng)的值的第一個字符由通配符(WILDCARD)開始，索引將不被采用。在這種情況下，ORACLE將使用全表掃描。

　　SELECTLODGING
　　FROMLODGING
　　WHEREMANAGERLIKE‘%HANMAN’;

1. 基礎(chǔ)表的選擇

　　基礎(chǔ)表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問)。根據(jù)優(yōu)化器的不同， SQL語句中基礎(chǔ)表的選擇是不一樣的。

　　如果你使用的是CBO (COST BASED OPTIMIZER)，優(yōu)化器會檢查SQL語句中的每個表的物理大小，索引的狀態(tài)，然后選用花費(fèi)最低的執(zhí)行路徑。

　　如果你用RBO (RULE BASED OPTIMIZER) ，并且所有的連接條件都有索引對應(yīng)，在這種情況下，基礎(chǔ)表就是FROM 子句中列在最后的那個表。

　　舉例：

　　SELECTA.NAME，B.MANAGER
　　FROM　WORKERA，
　　LODGINGB
　　WHERE　A.LODGING=B.LODING;

　　由于LODGING表的LODING列上有一個索引，而且WORKER表中沒有相比較的索引， WORKER表將被作為查詢中的基礎(chǔ)表。

　　2. 多個平等的索引

　　當(dāng)SQL語句的執(zhí)行路徑可以使用分布在多個表上的多個索引時， ORACLE會同時使用多個索引并在運(yùn)行時對它們的記錄進(jìn)行合并，檢索出僅對全部索引有效的記錄。

　　在ORACLE選擇執(zhí)行路徑時，唯一性索引的等級高于非唯一性索引。然而這個規(guī)則只有當(dāng)WHERE子句中索引列和常量比較才有效。如果索引列和其他表的索引類相比較。這種子句在優(yōu)化器中的等級是非常低的。

　　如果不同表中兩個想同等級的索引將被引用， FROM子句中表的順序?qū)Q定哪個會被率先使用。 FROM子句中最后的表的索引將有最高的優(yōu)先級。

　　如果相同表中兩個想同等級的索引將被引用， WHERE子句中最先被引用的索引將有最高的優(yōu)先級。

　　舉例：

　　DEPTNO上有一個非唯一性索引，EMP_CAT也有一個非唯一性索引。

　　SELECTENAME，
　　FROMEMP
　　WHEREDEPT_NO=20
　　ANDEMP_CAT=‘A’;
這里，DEPTNO索引將被最先檢索，然后同EMP_CAT索引檢索出的記錄進(jìn)行合并。執(zhí)行路徑如下：
　　TABLEACCESSBYROWIDONEMP
　　AND-EQUAL
　　INDEXRANGESCANONDEPT_IDX
　　INDEXRANGESCANONCAT_IDX
　　3. 等式比較和范圍比較
　　當(dāng)WHERE子句中有索引列， ORACLE不能合并它們，ORACLE將用范圍比較。
　　舉例：
　　DEPTNO上有一個非唯一性索引，EMP_CAT也有一個非唯一性索引。
　　SELECTENAME
　　FROMEMP
　　WHEREDEPTNO>20
　　ANDEMP_CAT=‘A’;
　　這里只有EMP_CAT索引被用到，然后所有的記錄將逐條與DEPTNO條件進(jìn)行比較。執(zhí)行路徑如下：
　　TABLEACCESSBYROWIDONEMP
　　INDEXRANGESCANONCAT_IDX
　　4. 不明確的索引等級
　　當(dāng)ORACLE無法判斷索引的等級高低差別，優(yōu)化器將只使用一個索引，它就是在WHERE子句中被列在最前面的。
　　舉例：
　　DEPTNO上有一個非唯一性索引，EMP_CAT也有一個非唯一性索引。
　　SELECTENAME
　　FROMEMP
　　WHEREDEPTNO>20
　　ANDEMP_CAT>‘A’;
　　這里， ORACLE只用到了DEPT_NO索引。執(zhí)行路徑如下：
　　TABLEACCESSBYROWIDONEMP
　　INDEXRANGESCANONDEPT_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是唯一性索引，但是由于它所做的是范圍比較，等級要比非唯一性索引的等式比較低!
　　5. 強(qiáng)制索引失效
　　如果兩個或以上索引具有相同的等級，你可以強(qiáng)制命令ORACLE優(yōu)化器使用其中的一個(通過它，檢索出的記錄數(shù)量少) .
　　舉例：
　　SELECTENAME
　　FROMEMP
　　WHEREEMPNO=7935
　　ANDDEPTNO+0=10/*DEPTNO上的索引將失效*/
　　ANDEMP_TYPE||‘’=‘A’/*EMP_TYPE上的索引將失效*/
　　這是一種相當(dāng)直接的提高查詢效率的辦法。但是你必須謹(jǐn)慎考慮這種策略，一般來說，只有在你希望單獨(dú)優(yōu)化幾個SQL時才能采用它。
　　這里有一個例子關(guān)于何時采用這種策略，
　　假設(shè)在EMP表的EMP_TYPE列上有一個非唯一性的索引而EMP_CLASS上沒有索引。
　　SELECTENAME
　　FROMEMP
　　WHEREEMP_TYPE=‘A’
　　ANDEMP_CLASS=‘X’;
　　優(yōu)化器會注意到EMP_TYPE上的索引并使用它。這是目前唯一的選擇。如果，一段時間以后，另一個非唯一性建立在EMP_CLASS上，優(yōu)化器必須對兩個索引進(jìn)行選擇，在通常情況下，優(yōu)化器將使用兩個索引并在他們的結(jié)果集合上執(zhí)行排序及合并。然而，如果其中一個索引(EMP_TYPE)接近于唯一性而另一個索引(EMP_CLASS)上有幾千個重復(fù)的值。排序及合并就會成為一種不必要的負(fù)擔(dān)。在這種情況下，你希望使優(yōu)化器屏蔽掉EMP_CLASS索引。
　　用下面的方案就可以解決問題。
　　SELECTENAME
　　FROMEMP
　　WHEREEMP_TYPE=‘A’
　　ANDEMP_CLASS||‘’=‘X’;

1. 避免在索引列上使用計(jì)算

　　WHERE子句中，如果索引列是函數(shù)的一部分。優(yōu)化器將不使用索引而使用全表掃描。

　　舉例：

　　低效：

　　SELECT…
　　FROMDEPT
　　WHERESAL*12>25000;

　　高效：

　　SELECT…
　　FROMDEPT
　　WHERESAL>25000/12;

　　：這是一個非常實(shí)用的規(guī)則，請務(wù)必牢記

　　2. 自動選擇索引

　　如果表中有兩個以上(包括兩個)索引，其中有一個唯一性索引，而其他是非唯一性。

　　在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引。

　　舉例：

　　SELECTENAME
　　FROMEMP
　　WHEREEMPNO=2326
　　ANDDEPTNO=20;

　　這里，只有EMPNO上的索引是唯一性的，所以EMPNO索引將用來檢索記錄。

　　TABLEACCESSBYROWIDONEMP
　　INDEXUNIQUESCANONEMP_NO_IDX

　　3. 避免在索引列上使用NOT

　　通常，我們要避免在索引列上使用NOT， NOT會產(chǎn)生在和在索引列上使用函數(shù)相同的影響。當(dāng)ORACLE“遇到”NOT，他就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描。

　　舉例：

　　低效： (這里，不使用索引)

　　SELECT…
　　FROMDEPT
　　WHEREDEPT_CODENOT=0;

　　高效： (這里，使用了索引)

　　SELECT…
　　FROMDEPT
　　WHEREDEPT_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)系操作符” 的觀點(diǎn)。

　　4. 用>=替代>

　　如果DEPTNO上有一個索引，

　　高效：

　　SELECT*
　　FROMEMP
　　WHEREDEPTNO>=4

　　低效：

　　SELECT*
　　FROMEMP
　　WHEREDEPTNO>3

　　兩者的區(qū)別在于，前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄。