1、東大阿爾派 王赟王赟2007年年4月月 Cost Based Optimizer(簡稱簡稱CBO)基于統(tǒng)計(jì)信息(成本)的優(yōu)化 Rule Based Optimizer(簡稱簡稱RBO)基于數(shù)據(jù)字典(規(guī)則)的優(yōu)化，在以后的版本中將不支持(10g及以后) 設(shè)置缺省的優(yōu)化器設(shè)置缺省的優(yōu)化器,可以通過對(duì)可以通過對(duì)init.ora文件文件中中OPTIMIZER_MODE參數(shù)的各種聲明參數(shù)的各種聲明 根據(jù)數(shù)據(jù)字典查詢有無可用的索引，如果根據(jù)數(shù)據(jù)字典查詢有無可用的索引，如果有則使用，否則不使用有則使用，否則不使用 不同的訪問方法有預(yù)定好的優(yōu)先級(jí)，選擇不同的訪問方法有預(yù)定好的優(yōu)先級(jí)，選擇優(yōu)先級(jí)高的執(zhí)行方法優(yōu)先

2、級(jí)高的執(zhí)行方法 需要收集統(tǒng)計(jì)信息需要收集統(tǒng)計(jì)信息 表有多少行，占用多少數(shù)據(jù)塊表有多少行，占用多少數(shù)據(jù)塊 列有多少個(gè)列有多少個(gè)Null值、不同值值、不同值 列的最大值和最小值，及值的分布情況列的最大值和最小值，及值的分布情況 索引的層次、結(jié)點(diǎn)數(shù)、葉結(jié)點(diǎn)數(shù)，及行的索引的層次、結(jié)點(diǎn)數(shù)、葉結(jié)點(diǎn)數(shù)，及行的分布狀況分布狀況(Cluster) 根據(jù)一定算法算出一個(gè)成本值，選擇成本根據(jù)一定算法算出一個(gè)成本值，選擇成本值最低的執(zhí)行方法，不一定使用索引。值最低的執(zhí)行方法，不一定使用索引。訪問Table的方式ORACLE 采用兩種訪問表中記錄的方式采用兩種訪問表中記錄的方式:a. 全表掃描全表掃描 全表掃描就是順序

3、地訪問表中每條記錄全表掃描就是順序地訪問表中每條記錄. ORACLE采用一次讀入采用一次讀入多個(gè)數(shù)據(jù)塊多個(gè)數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描的方式優(yōu)化全表掃描. b. 通過通過ROWID訪問表訪問表 你可以采用基于你可以采用基于ROWID的訪問方式情況的訪問方式情況,提高訪問表的效率提高訪問表的效率, , ROWID包含了表中記錄的物理位置信息包含了表中記錄的物理位置信息.ORACLE采用索引采用索引(INDEX)實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系之間的聯(lián)系. 通常索引提通常索引提供了快速訪問供了快速訪問ROWID的方法的

4、方法,因此那些基于索引列的查詢就可以得到因此那些基于索引列的查詢就可以得到性能上的提高性能上的提高 索引掃描不一定是最快的索引掃描不一定是最快的(如小表如小表)CBO中經(jīng)常會(huì)選擇全表掃描中經(jīng)常會(huì)選擇全表掃描(如小表如小表)當(dāng)當(dāng)Selective (SELECT MIN(B.ROWID) FROM SPJK_ORDER_PARA B WHERE A.ORDER_ID = B.ORDER_ID AND A.PARA_ID = B.PARA_ID); 在含有子查詢的在含有子查詢的SQL語句中語句中,要特別注意減要特別注意減少對(duì)表的查詢少對(duì)表的查詢.低效低效 SELECT * FROM INTER_S

5、ERVICE_ORDER A WHERE A.SO_NBR IN (SELECT B.SO_NBR FROM TF_LOG B WHERE B.IF_ALLOW = G) AND A.PROD_ID IN (SELECT B.COMM_SERV_SPEC_ID FROM TF_LOG B WHERE B.IF_ALLOW = G)高效高效SELECT * FROM INTER_SERVICE_ORDER A WHERE (A.SO_NBR, A.PROD_ID) IN (SELECT B.SO_NBR, B.COMM_SERV_SPEC_ID FROM TF_LOG B WHERE B.IF_

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

7、)的唯一的唯一性驗(yàn)證性驗(yàn)證. 除了那些除了那些LONG或或LONG RAW數(shù)據(jù)類型數(shù)據(jù)類型, 你可你可以索引幾乎所有的列以索引幾乎所有的列. 通常通常, 在大型表中使用索引在大型表中使用索引特別有效特別有效. 雖然使用索引能得到查詢效率的提高雖然使用索引能得到查詢效率的提高,但是我們也但是我們也必須注意到它的代價(jià)必須注意到它的代價(jià). 索引需要空間來存儲(chǔ)索引需要空間來存儲(chǔ),也需也需要定期維護(hù)要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被每當(dāng)有記錄在表中增減或索引列被修改時(shí)修改時(shí), 索引本身也會(huì)被修改索引本身也會(huì)被修改. 這意味著每條記錄這意味著每條記錄的的INSERT , DELETE , UPD

8、ATE將為此多付出將為此多付出4 , 5 次的磁盤次的磁盤I/O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和因?yàn)樗饕枰~外的存儲(chǔ)空間和處理處理,那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢慢 定期的重構(gòu)索引是有必要的定期的重構(gòu)索引是有必要的. ALTER INDEX REBUILD 索引能提高速度的關(guān)鍵就是索引所占的空索引能提高速度的關(guān)鍵就是索引所占的空間要比表小得多間要比表小得多 索引的列不要太多，要選擇一些索引的列不要太多，要選擇一些selective比比較低的列建較低的列建B-tree索引，選擇索引，選擇selective高的高的列建列建bitmap索引索引(在

9、更新比較多的表不不要在更新比較多的表不不要建建bitmap索引索引) 將將selective較低的列放在前面較低的列放在前面 在更新不多的表上建索引時(shí)，可以考在更新不多的表上建索引時(shí)，可以考慮用慮用compress選擇，以節(jié)約索引的空選擇，以節(jié)約索引的空間間另外創(chuàng)建分區(qū)表另外創(chuàng)建分區(qū)表一般記錄數(shù)在一般記錄數(shù)在50萬以上的可以考慮創(chuàng)萬以上的可以考慮創(chuàng)建分區(qū)表建分區(qū)表基礎(chǔ)表的選擇 基礎(chǔ)表基礎(chǔ)表(Driving Table)是指被最先訪問的表是指被最先訪問的表(通常通常以全表掃描的方式被訪問以全表掃描的方式被訪問). 根據(jù)優(yōu)化器的不同根據(jù)優(yōu)化器的不同, SQL語句中基礎(chǔ)表的選擇是不一樣的語句中基礎(chǔ)表

10、的選擇是不一樣的. 如果你使用的是如果你使用的是CBO (COST BASED OPTIMIZER),優(yōu)化器會(huì)檢查優(yōu)化器會(huì)檢查SQL語句中的每個(gè)表語句中的每個(gè)表的物理大小的物理大小,索引的狀態(tài)索引的狀態(tài),然后選用花費(fèi)最低的執(zhí)行然后選用花費(fèi)最低的執(zhí)行路徑路徑. 如果你用如果你用RBO (RULE BASED OPTIMIZER) , 并且所有的連接條件都有索引對(duì)并且所有的連接條件都有索引對(duì)應(yīng)應(yīng), 在這種情況下在這種情況下, 基礎(chǔ)表就是基礎(chǔ)表就是FROM 子句中列在子句中列在最后的那個(gè)表最后的那個(gè)表. 當(dāng)當(dāng)SQL語句的執(zhí)行路徑可以使用分布在多個(gè)表上語句的執(zhí)行路徑可以使用分布在多個(gè)表上的多個(gè)索引時(shí)的

11、多個(gè)索引時(shí), ORACLE會(huì)同時(shí)使用多個(gè)索引并會(huì)同時(shí)使用多個(gè)索引并在運(yùn)行時(shí)對(duì)它們的記錄進(jìn)行合并在運(yùn)行時(shí)對(duì)它們的記錄進(jìn)行合并, 檢索出僅對(duì)全部檢索出僅對(duì)全部索引有效的記錄索引有效的記錄. 在在ORACLE選擇執(zhí)行路徑時(shí)選擇執(zhí)行路徑時(shí),唯一性索引的等唯一性索引的等級(jí)高于非唯一性索引級(jí)高于非唯一性索引. 然而這個(gè)規(guī)則只有當(dāng)然而這個(gè)規(guī)則只有當(dāng)WHERE子句中索引列和常量比較才有效子句中索引列和常量比較才有效.如果索如果索引列和其他表的索引類相比較引列和其他表的索引類相比較. 這種子句在優(yōu)化器這種子句在優(yōu)化器中的等級(jí)是非常低的中的等級(jí)是非常低的. 如果不同表中兩個(gè)想同等級(jí)的索引將被引如果不同表中兩個(gè)想同

12、等級(jí)的索引將被引用用, FROM子句中表的順序?qū)Q定哪個(gè)會(huì)被子句中表的順序?qū)Q定哪個(gè)會(huì)被率先使用率先使用. FROM子句中最后的表的索引將子句中最后的表的索引將有最高的優(yōu)先級(jí)有最高的優(yōu)先級(jí). 如果相同表中兩個(gè)想同等級(jí)的索引將被如果相同表中兩個(gè)想同等級(jí)的索引將被引用引用, WHERE子句中最先被引用的索引將子句中最先被引用的索引將有最高的優(yōu)先級(jí)有最高的優(yōu)先級(jí). 當(dāng)當(dāng)ORACLE無法判斷索引的等級(jí)高低差別無法判斷索引的等級(jí)高低差別,優(yōu)化器優(yōu)化器將只使用一個(gè)索引將只使用一個(gè)索引,它就是在它就是在WHERE子句中被列子句中被列在最前面的在最前面的. 舉例舉例: DEPTNO上有一個(gè)非唯一性索引上有一個(gè)

13、非唯一性索引,EMP_CAT也有也有一個(gè)非唯一性索引一個(gè)非唯一性索引. SELECT ENAME FROM EMP WHERE DEPTNO 20 AND EMP_CAT A; 這里這里, ORACLE只用到了只用到了DEPT_NO索引索引. 執(zhí)行路徑如下執(zhí)行路徑如下: TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON DEPT_IDX 如果兩個(gè)或以上索引具有相同的等級(jí)如果兩個(gè)或以上索引具有相同的等級(jí),你可以強(qiáng)制命令你可以強(qiáng)制命令ORACLE優(yōu)化器使用其中的一個(gè)優(yōu)化器使用其中的一個(gè)(通過它通過它,檢索出的記錄數(shù)檢索出的記錄數(shù)量少量少) . 如果其

14、中一個(gè)索引接近于唯一性而另一個(gè)索引上有幾如果其中一個(gè)索引接近于唯一性而另一個(gè)索引上有幾千個(gè)重復(fù)的值千個(gè)重復(fù)的值. 排序及合并就會(huì)成為一種不必要的負(fù)擔(dān)排序及合并就會(huì)成為一種不必要的負(fù)擔(dān). 在在這種情況下這種情況下,你希望使優(yōu)化器屏蔽掉有重復(fù)值索引你希望使優(yōu)化器屏蔽掉有重復(fù)值索引 . WHERE子句中，如果索引列是函數(shù)的一子句中，如果索引列是函數(shù)的一部分優(yōu)化器將不使用索引而使用全表掃部分優(yōu)化器將不使用索引而使用全表掃描描 SELECT * FROM SO_ACC_NBR WHERE NXX_NBR|LINE_NBR=87881434 如果表中有兩個(gè)以上（包括兩個(gè)）索引，其中有一個(gè)唯如果表中有兩個(gè)以

15、上（包括兩個(gè)）索引，其中有一個(gè)唯一性索引，而其他是非唯一性一性索引，而其他是非唯一性 在這種情況下，在這種情況下，ORACLE將使用唯一性索引而完全忽將使用唯一性索引而完全忽略非唯一性索引略非唯一性索引. 避免在索引中使用任何可以為空的列，避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引將無法使用該索引 對(duì)于單列索對(duì)于單列索引，如果列包含空值，索引中將不存在此引，如果列包含空值，索引中將不存在此記錄記錄. 對(duì)于復(fù)合索引，如果每個(gè)列都為空，對(duì)于復(fù)合索引，如果每個(gè)列都為空，索引中同樣不存在此記錄索引中同樣不存在此記錄.如果至少有一如果至少有一個(gè)列不為空，則記錄存在于索引中個(gè)列不為空，

16、則記錄存在于索引中 . 如果唯一性索引建立在表的如果唯一性索引建立在表的A列和列和B列上列上, 并且表并且表中存在一條記錄的中存在一條記錄的A,B值為值為(123,null) , ORACLE將不接受下一條具有相同將不接受下一條具有相同A,B值（值（123,null）的記）的記錄錄(插入插入). 然而如果所有的索引列都為空，然而如果所有的索引列都為空，ORACLE將認(rèn)為整個(gè)鍵值為空而空不等于空將認(rèn)為整個(gè)鍵值為空而空不等于空. 因因此你可以插入此你可以插入1000條具有相同鍵值的記錄條具有相同鍵值的記錄,當(dāng)然它當(dāng)然它們都是空們都是空. 因?yàn)榭罩挡淮嬖谟谒饕兄幸驗(yàn)榭罩挡淮嬖谟谒饕兄?所以所以W

17、HERE子句中子句中對(duì)索引列進(jìn)行空值比較將使對(duì)索引列進(jìn)行空值比較將使ORACLE停用該索引停用該索引. 如果索引是建立在多個(gè)列上如果索引是建立在多個(gè)列上, 只有在它的第只有在它的第一個(gè)列一個(gè)列(leading column)被被where子句引用時(shí)子句引用時(shí),優(yōu)化器才會(huì)選擇使用該索引優(yōu)化器才會(huì)選擇使用該索引. 對(duì)于表的訪問對(duì)于表的訪問,可以使用兩種可以使用兩種Hints:FULL 和和 ROWID FULL hint 告訴告訴ORACLE使用全表掃描的使用全表掃描的方式訪問指定表方式訪問指定表. select /*+full(so_nbr)*/* from work_item_so where

18、 local_net_id =290 select /*+index(so_nbr)*/ * from work_item_so where sts =C 在不使用在不使用hint的情況下的情況下, 以上的查詢應(yīng)該也會(huì)使用以上的查詢應(yīng)該也會(huì)使用索引索引,然而然而,如果該索引的重復(fù)值過多而你的優(yōu)化如果該索引的重復(fù)值過多而你的優(yōu)化器是器是CBO, 優(yōu)化器就可能忽略索引優(yōu)化器就可能忽略索引. 在這種情況下在這種情況下, 你可以用你可以用INDEX hint強(qiáng)制強(qiáng)制ORACLE使用該索引使用該索引 . 當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí)當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí), ORACLE自動(dòng)對(duì)自動(dòng)對(duì)列進(jìn)行簡單的類型轉(zhuǎn)換列進(jìn)行簡單的類型轉(zhuǎn)換 . select *