Oracle sql 性能優(yōu)化調(diào)整
　　1. 選用適合的ORACLE優(yōu)化器
　　ORACLE的優(yōu)化器共有3種：
　　a. RULE (基于規(guī)則)
　　b. COST (基于成本)
　　c. CHOOSE (選擇性)
　　設置缺省的優(yōu)化器，可以通過對init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明，如RULE，COST，CHOOSE，ALL_ROWS，F(xiàn)IRST_ROWS . 你當然也在SQL句級或是會話(session)級對其進行覆蓋。
　　為了使用基于成本的優(yōu)化器(CBO， Cost-Based Optimizer) ， 你必須經(jīng)常運行analyze 命令，以增加數(shù)據(jù)庫中的對象統(tǒng)計信息(object statistics)的準確性。
　　如果數(shù)據(jù)庫的優(yōu)化器模式設置為選擇性(CHOOSE)，那么實際的優(yōu)化器模式將和是否運行過analyze命令有關。 如果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語句
　　為了不重復解析相同的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)被解析的語句以及最好的執(zhí)行路徑。 ORACLE的這個功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用。
可惜的是ORACLE只對簡單的表提供高速緩沖(cache buffering) ，這個功能并不適用于多表連接查詢。數(shù)據(jù)庫管理員必須在init.ora中為這個區(qū)域設置合適的參數(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語句能否在這兩個用戶之間共享。
　　C. 兩個SQL語句中必須使用相同的名字的綁定變量(bind variables)
　　例如：第一組的兩個SQL語句是相同的(可以共享)，而第二組中的兩個語句是不同的(即使在運行時，賦于不同的綁定變量相同的值)
　　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子句中寫在最后的表(基礎表 driving table)將被最先處理。 在FROM子句中包含多個表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎表。當ORACLE處理多個表時， 會運用排序及合并的方式連接它們。首先，掃描第一個表(FROM子句中最后的那個表)并對記錄進行派序，然后掃描第二個表(FROM子句中最后第二個表)，最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并。
　　例如：
　　表 TAB1 16，384 條記錄
　　表 TAB2 1 條記錄
　　選擇TAB2作為基礎表 (最好的方法)
　　select count(*) from tab1，tab2 執(zhí)行時間0.96秒
　　選擇TAB2作為基礎表 (不佳的方法)
　　select count(*) from tab2，tab1 執(zhí)行時間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
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子句中避免使用 ‘ * ’
　　當你想在SELECT子句中列出所有的COLUMN時，使用動態(tài)SQL列引用 ‘*’ 是一個方便的方法。不幸的是，這是一個非常低效的方法。 實際上，ORACLE在解析的過程中， 會將‘*’ 依次轉(zhuǎn)換成所有的列名， 這個工作是通過查詢數(shù)據(jù)字典完成的， 這意味著將耗費更多的時間。
　　3. 減少訪問數(shù)據(jù)庫的次數(shù)
　　當執(zhí)行每條SQL語句時， ORACLE在內(nèi)部執(zhí)行了許多工作： 解析SQL語句， 估算索引的利用率， 綁定變量 ， 讀數(shù)據(jù)塊等等。 由此可見， 減少訪問數(shù)據(jù)庫的次數(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中重新設置ARRAYSIZE參數(shù)， 可以增加每次數(shù)據(jù)庫訪問的檢索數(shù)據(jù)量 ，建議值為200.
　　4. 使用DECODE函數(shù)來減少處理時間
　　使用DECODE函數(shù)可以避免重復掃描相同記錄或重復連接相同的表。
　　例如：
　　SELECTCOUNT(*)，SUM(SAL)
　　FROM　EMP
　　WHEREDEPT_NO=0020
　　ANDENAMELIKE　‘SMITH%’;
　　SELECTCOUNT(*)，SUM(SAL)
　　FROM　EMP
　　WHEREDEPT_NO=0030
　　ANDENAMELIKE　‘SMITH%’;
　　你可以用DECODE函數(shù)高效地得到相同結果
　　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ù)也可以運用于GROUP BY 和ORDER BY子句中。
　　5. 整合簡單，無關聯(lián)的數(shù)據(jù)庫訪問
　　如果你有幾個簡單的數(shù)據(jù)庫查詢語句，你可以把它們整合到一個查詢中(即使它們之間沒有關系)
　　例如：
　　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. 刪除重復記錄
　　最高效的刪除重復記錄方法 ( 因為使用了ROWID)
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
　　 　 　 　 　 　FROM EMP X
　　 　 　 　 　 　WHERE X.EMP_NO = E.EMP_NO);
　　2. 用TRUNCATE替代DELETE
　　當刪除表中的記錄時，在通常情況下， 回滾段(rollback segments ) 用來存放可以被恢復的信息。 如果你沒有COMMIT事務，ORACLE會將數(shù)據(jù)恢復到刪除之前的狀態(tài)(準確地說是恢復到執(zhí)行刪除命令之前的狀況)
　　而當運用TRUNCATE時， 回滾段不再存放任何可被恢復的信息。當命令運行后，數(shù)據(jù)不能被恢復。因此很少的資源被調(diào)用，執(zhí)行時間也會很短。
　　(譯者按： TRUNCATE只在刪除全表適用，TRUNCATE是DDL不是DML)
　　３. 盡量多使用COMMIT
　　只要有可能，在程序中盡量多使用COMMIT， 這樣程序的性能得到提高，需求也會因為COMMIT所釋放的資源而減少：COMMIT所釋放的資源：
　　a. 回滾段上用于恢復數(shù)據(jù)的信息。
　　b. 被程序語句獲得的鎖
　　c. redo log buffer 中的空間
　　d. ORACLE為管理上述3種資源中的內(nèi)部花費
　　(譯者按： 在使用COMMIT時必須要注意到事務的完整性，現(xiàn)實中效率和事務完整性往往是魚和熊掌不可得兼)
　　４. 計算記錄條數(shù)
　　和一般的觀點相反， count(*) 比count(1)稍快 ， 當然如果可以通過索引檢索，對索引列的計數(shù)仍舊是最快的。 例如 COUNT(EMPNO)
　　(譯者按： 在CSDN論壇中，曾經(jīng)對此有過相當熱烈的討論， 作者的觀點并不十分準確，通過實際的測試，上述三種方法并沒有顯著的性能差別)
　　５. 用Where子句替換HAVING子句
　　避免使用HAVING子句， HAVING 只會在檢索出所有記錄之后才對結果集進行過濾。 這個處理需要排序，總計等操作。 如果能通過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() 等等。 除此而外，一般的條件應該寫在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)
　　當在SQL語句中連接多個表時， 請使用表的別名并把別名前綴于每個Column上。這樣一來，就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。
　　(Column歧義指的是由于SQL中不同的表具有相同的Column名，當SQL語句中出現(xiàn)這個Column時，SQL解析器無法判斷這個Column的歸屬)
　　2. 用EXISTS替代IN
　　在許多基于基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯(lián)接。在這種情況下， 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。
　　低效：
　　SELECT*
　　FROMEMP(基礎表)
　　WHEREEMPNO>0
　　ANDDEPTNOIN(SELECTDEPTNO
　　FROMDEPT
　　WHERELOC=‘MELB’)
　　高效：
　　SELECT*
　　FROMEMP(基礎表)
　　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都是最低效的 (因為它對子查詢中的表執(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是怎么樣連接表，使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱。

　　你需要按照從里到外，從上到下的次序解讀分析的結果。 EXPLAIN PLAN分析的結果是用縮進的格式排列的， 最內(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í)行結果*/
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工具。也許喜歡圖形化界面的朋友們可以選用它們。

　　2. 用索引提高效率

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

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

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

　　定期的重構索引是有必要的。

　　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 索引將通過索引唯一掃描的方式被訪問 ， 獲得相對應的ROWID， 通過ROWID訪問表的方式執(zhí)行下一步檢索。

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

　　下面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子句中， 如果索引列所對應的值的第一個字符由通配符(WILDCARD)開始， 索引將不被采用。在這種情況下，ORACLE將使用全表掃描。

　　SELECTLODGING
　　FROMLODGING
　　WHEREMANAGERLIKE‘%HANMAN’;

1. 基礎表的選擇

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

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

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

　　舉例：

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

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

　　2. 多個平等的索引

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

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

1. 避免在索引列上使用計算

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

　　舉例：

　　低效：

　　SELECT…
　　FROMDEPT
　　WHERESAL*12>25000;

　　高效：

　　SELECT…
　　FROMDEPT
　　WHERESAL>25000/12;

　?。哼@是一個非常實用的規(guī)則，請務必牢記

　　2. 自動選擇索引

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

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

　　舉例：

　　SELECTENAME
　　FROMEMP
　　WHEREEMPNO=2326
　　ANDDEPTNO=20;

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

　　TABLEACCESSBYROWIDONEMP
　　INDEXUNIQUESCANONEMP_NO_IDX

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

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

　　舉例：

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

　　SELECT…
　　FROMDEPT
　　WHEREDEPT_CODENOT=0;

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

　　SELECT…
　　FROMDEPT
　　WHEREDEPT_CODE>0;

　　需要注意的是，在某些時候， ORACLE優(yōu)化器會自動將NOT轉(zhuǎ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）

　　兩者的效率完全一樣，也許這符合作者關于“ 在某些時候， ORACLE優(yōu)化器會自動將NOT轉(zhuǎn)化成相對應的關系操作符” 的觀點。

　　4. 用>=替代>

　　如果DEPTNO上有一個索引，

　　高效：

　　SELECT*
　　FROMEMP
　　WHEREDEPTNO>=4

　　低效：

　　SELECT*
　　FROMEMP
　　WHEREDEPTNO>3

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

1. 避免在索引列上使用計算

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

　　舉例：

　　低效：

　　SELECT…
　　FROMDEPT
　　WHERESAL*12>25000;

　　高效：

　　SELECT…
　　FROMDEPT
　　WHERESAL>25000/12;

　?。哼@是一個非常實用的規(guī)則，請務必牢記

　　2. 自動選擇索引

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

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

　　舉例：

　　SELECTENAME
　　FROMEMP
　　WHEREEMPNO=2326
　　ANDDEPTNO=20;

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

　　TABLEACCESSBYROWIDONEMP
　　INDEXUNIQUESCANONEMP_NO_IDX

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

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

　　舉例：

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

　　SELECT…
　　FROMDEPT
　　WHEREDEPT_CODENOT=0;

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

　　SELECT…
　　FROMDEPT
　　WHEREDEPT_CODE>0;

　　需要注意的是，在某些時候， ORACLE優(yōu)化器會自動將NOT轉(zhuǎ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）

　　兩者的效率完全一樣，也許這符合作者關于“ 在某些時候， ORACLE優(yōu)化器會自動將NOT轉(zhuǎn)化成相對應的關系操作符” 的觀點。

　　4. 用>=替代>

　　如果DEPTNO上有一個索引，

　　高效：

　　SELECT*
　　FROMEMP
　　WHEREDEPTNO>=4

　　低效：

　　SELECT*
　　FROMEMP
　　WHEREDEPTNO>3

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

 {http://blog.chinaunix.net/u/20483/showart_546882.html}