ORACLE中的執(zhí)行計劃

./Oracle執(zhí)行計劃1,什么是執(zhí)行計劃所謂執(zhí)行計劃,顧名思義,就是對一個查詢?nèi)蝿?wù),做出一份怎樣去完成任務(wù)的詳細(xì)方案。舉個生活中的例子,我從XX要去英國,我可以選擇先去XX然后轉(zhuǎn)機,也可以先去轉(zhuǎn)機,或者去XX也可以。但是到底怎樣去英國劃算,也就是我的費用最少,這是一件值得考究的事情。同樣對于查詢而言,我們提交的SQL僅僅是描述出了我們的目的地是英國,但至于怎么去,通常我們的SQL中是沒有給出提示信息的,是由數(shù)據(jù)庫來決定的。我們先簡單的看一個執(zhí)行計劃的對比：SQL>setautotracetraceonly執(zhí)行計劃一：SQL>selectcount<*>fromt;

COUNT<*>

24815

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10SORT<AGGREGATE>

21TABLEAccess<FULL>OF'T'執(zhí)行計劃二：SQL>selectcount<*>fromt;

COUNT<*>

24815

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE<Cost=26Card=1>

10SORT<AGGREGATE>

21INDEX<FULLSCAN>OF'T_INDEX'<NON-UNIQUE><Cost=26Card=28180>這兩個執(zhí)行計劃中,第一個表示求和是通過進(jìn)行全表掃描來做的,把整個表中數(shù)據(jù)讀入內(nèi)存來逐條累加;第二個表示根據(jù)表中索引,把整個索引讀進(jìn)內(nèi)存來逐條累加,而不用去讀表中的數(shù)據(jù)。但是這兩種方式到底哪種快呢?通常來說可能二比一快,但也不是絕對的。這是一個很簡單的例子演示執(zhí)行計劃的差異。對于復(fù)雜的SQL<表連接、嵌套子查詢等>,執(zhí)行計劃可能幾十種甚至上百種,但是到底那種最好呢?我們事前并不知道,數(shù)據(jù)庫本身也不知道,但是數(shù)據(jù)庫會根據(jù)一定的規(guī)則或者統(tǒng)計信息<statistics>去選擇一個執(zhí)行計劃,通常來說選擇的是比較優(yōu)的,但也有選擇失誤的時候,這就是這次討論的價值所在。

Oracle優(yōu)化器模式Oracle優(yōu)化器有兩大類,基于規(guī)則的和基于代價的,在SQLPLUS中我們可以查看init文件中定義的缺省的優(yōu)化器模式。SQL>showparametersoptimizer_mode

NAMETYPEVALUE

optimizer_modestringCHOOSE

SQL>這是Oracle企業(yè)版,我們可以看出,默認(rèn)安裝后數(shù)據(jù)庫優(yōu)化器模式為CHOOSE,我們還可以設(shè)置為RULE、FIRST_ROWS,ALL_ROWS?？梢栽趇nit文件中對整個instance的所有會話設(shè)置,也可以單獨對某個會話設(shè)置：SQL>ALTERSESSIONSEToptimizer_mode=RULE;

會話已更改。

SQL>ALTERSESSIONSEToptimizer_mode=FIRST_ROWS;

會話已更改。

SQL>ALTERSESSIONSEToptimizer_mode=ALL_ROWS;

會話已更改。基于規(guī)則的查詢,數(shù)據(jù)庫根據(jù)表和索引等定義信息,按照一定的規(guī)則來產(chǎn)生執(zhí)行計劃;基于代價的查詢,數(shù)據(jù)庫根據(jù)搜集的表和索引的數(shù)據(jù)的統(tǒng)計信息<通過analyze命令或者使用dbms_stats包來搜集>綜合來決定選取一個數(shù)據(jù)庫認(rèn)為最優(yōu)的執(zhí)行計劃<實際上不一定最優(yōu)>。RULE是基于規(guī)則的,CHOOSE表示如果查詢的表存在搜集的統(tǒng)計信息則基于代價來執(zhí)行<在CHOOSE模式下Oracle采用的是FIRST_ROWS>,否則基于規(guī)則來執(zhí)行。在基于代價的兩種方式中,FIRST_ROWS指執(zhí)行計劃采用最少資源盡快的返回部分結(jié)果給客戶端,對于排序分頁頁顯示這種查詢尤其適用,ALL_ROWS指以總體消耗資源最少的方式返回結(jié)果給客戶端?；谝?guī)則的模式下,數(shù)據(jù)庫的執(zhí)行計劃通常比較穩(wěn)定。但在基于代價的模式下,我們才有更大的機會選擇最優(yōu)的執(zhí)行計劃。也由于Oracle的很多查詢方面的特性必須在基于代價的模式下才能體現(xiàn)出來,所以我們通常不選擇RULE<并且Oracle宣稱從Oracle10i版本數(shù)據(jù)庫開始將不再支持RULE>。既然是基于代價的模式,也就是說執(zhí)行計劃的選擇是根據(jù)表、索引等定義和數(shù)據(jù)的統(tǒng)計信息來決定的,這個統(tǒng)計信息是根據(jù)analyze命令或者dbms_stats包來定期搜集的。首先存在著一種可能,就是由于搜集信息是一個很消耗資源和時間的動作,尤其當(dāng)表數(shù)據(jù)量很大的時候,因為搜集信息是對整個表數(shù)據(jù)進(jìn)行重新的完全統(tǒng)計,所以這是我們必須慎重考慮的問題。我們只能在服務(wù)器空閑的時候定期的進(jìn)行信息搜集。這說明我們在一段時期內(nèi),統(tǒng)計信息可能和數(shù)據(jù)庫本身的數(shù)據(jù)并不吻合;另外就是Oracle的統(tǒng)計數(shù)據(jù)本身也存在著不精確部分<詳細(xì)參考OracleDOCUMENT>,更重要的一個問題就是與時統(tǒng)計數(shù)據(jù)相對已經(jīng)比較準(zhǔn)確,但是Oracle的優(yōu)化器的選擇也并不是始終是最優(yōu)的方案。這也倚賴于Oracle對不同執(zhí)行計劃的代價的計算規(guī)則<我們通常是無法知道具體的計算規(guī)則的>。這好比我們決定從XX還是從去英國,車票、機票等實際價格到底是怎么核算出來的我們并不知道,或者說我們現(xiàn)在了解的價格信息,在我們乘車前往的時候,真實價格跟我們的預(yù)算已經(jīng)發(fā)生了變化。所有的因素,都將影響我們的整個開銷。執(zhí)行計劃穩(wěn)定性能帶給我們什么Oracle存在著執(zhí)行計劃選擇失誤的可能。這也是我們經(jīng)常遇見的一些現(xiàn)象,比如總有人說我的程序在測試數(shù)據(jù)庫中跑的很好,但在產(chǎn)品數(shù)據(jù)庫上就是跑的很差,甚至后者硬件條件比前者還好,這到底是為什么?硬件資源、統(tǒng)計信息、參數(shù)設(shè)置都可能對執(zhí)行計劃產(chǎn)生影響。由于因素太多,我們總是對未來懷著一種莫名的恐懼,我的產(chǎn)品數(shù)據(jù)庫上線后到底跑的好不好?于是Oracle提供了一種穩(wěn)定執(zhí)行計劃的能力,也就是把在測試環(huán)境中的運行良好的執(zhí)行計劃所產(chǎn)生的OUTLINES移植到產(chǎn)品數(shù)據(jù)庫,使得執(zhí)行計劃不會隨著其他因素的變化而變化。那么OUTLINES是什么呢?先要介紹一個內(nèi)容,Oracle提供了在SQL中使用HINTS來引導(dǎo)優(yōu)化器產(chǎn)生我們想要的執(zhí)行計劃的能力。這在多表連接、復(fù)雜查詢中特別有效。HINTS的類型很多,可以設(shè)置優(yōu)化器目標(biāo)<RULE、CHOOSE、FIRST_ROWS、ALL_ROWS>,可以指定表連接的順序,可以指定使用哪個表的哪個索引等等,可以對SQL進(jìn)行很多精細(xì)的控制。通過這種方式產(chǎn)生我們想要的執(zhí)行計劃的這些HINTS,Oracle可以存儲這些HINTS,我們稱之為OUTLINES。通過STOREOUTLINES可以使得我們擁有以后產(chǎn)生相同執(zhí)行計劃的能力,也就是使我們擁有了穩(wěn)定執(zhí)行計劃的能力。這里想給出一個附加的說明就是,實際上,我們通過工具改寫SQL,比如使用SQLEXPERT改寫后的SQL,這些不僅僅是加了HINTS而且文本都已經(jīng)發(fā)生了變化的SQL,也可以存儲OUTLINES,并可被應(yīng)用到應(yīng)用中。但這不是一定生效,我們必須測試檢查是否生效。但由于就算給了錯誤的OUTLINES,數(shù)據(jù)庫在執(zhí)行的時候,也只是忽略過去重新生成執(zhí)行計劃而不會返回錯誤,所以我們才敢放心的這么使用。當(dāng)然在Oracle文檔中并沒有指明可以這樣做,文檔中只是說明,如果存在OUTLINES的同時又在SQL中加了HINTS,則會使用OUTLINES而忽略HINTS。這個功能在LECCO將發(fā)布的產(chǎn)品中會使用這一功能,這樣可以將SQLEXPERT的改寫SQL的能力和穩(wěn)定執(zhí)行計劃的能力結(jié)合起來,那么我們就對不能更改源代碼的應(yīng)用具有了相當(dāng)強大的SQL優(yōu)化能力。也許我們會有疑問,假如穩(wěn)定了執(zhí)行計劃,那還搜集統(tǒng)計信息干嗎?這是因為幾個原因造成的,首先,現(xiàn)在的執(zhí)行計劃對于未來發(fā)生了變化的數(shù)據(jù)未必就是合適的,存在著當(dāng)前的執(zhí)行計劃不滿足未來數(shù)據(jù)的變化后的效率,而新的統(tǒng)計信息的情況下所產(chǎn)生的執(zhí)行計劃也并不是全部都合理的。那這個時候,我們可以采用新搜集的統(tǒng)計信息,但是卻對新統(tǒng)計信息下不良的執(zhí)行計劃采用Oracle提供的執(zhí)行計劃穩(wěn)定性這個能力固定執(zhí)行計劃,這樣結(jié)合起來我們可以建立滿意的高效的數(shù)據(jù)庫運行環(huán)境。我們還需要關(guān)注的一個東西,Oracle提供的dbms_stats包除了具有搜集統(tǒng)計信息的能力,還具有把數(shù)據(jù)庫中統(tǒng)計信息<statistics>export/import的能力,還具有只搜集統(tǒng)計信息而使得統(tǒng)計信息不應(yīng)用于數(shù)據(jù)庫的能力<把統(tǒng)計信息搜集到一個特定的表中而不是立即生效>,在這個基礎(chǔ)上我們就可以把統(tǒng)計信息export出來再import到一個測試環(huán)境中,再運行我們的應(yīng)用,在測試環(huán)境中我們觀察最新的統(tǒng)計信息會導(dǎo)致哪些執(zhí)行計劃發(fā)生變化<DBEXPERT的PlanVersionTracer是模擬不同環(huán)境并自動檢查不同環(huán)境中執(zhí)行計劃變化的工具>,是變好了還是變差了。我們可以把變差的這一部分在測試環(huán)境中使用hints或者利用工具<SQLEXPERT是在重寫SQL這一領(lǐng)域目前最強有力的工具>產(chǎn)生良好的執(zhí)行計劃的SQL,利用這些SQL可以產(chǎn)生OUTLINES,然后在產(chǎn)品數(shù)據(jù)庫應(yīng)用最新的統(tǒng)計信息的同時移植進(jìn)這些OUTLINES。最后說一下我們不得不使用執(zhí)行計劃穩(wěn)定性能力的場合。我們假定Oracle的優(yōu)化器的選擇都是準(zhǔn)確的,但是優(yōu)化器選擇的基礎(chǔ)就是我們的SQL,這些SQL才從根本上決定了運行效率,這是更重要的一個優(yōu)化的環(huán)節(jié)。SQL是基礎(chǔ)<當(dāng)然數(shù)據(jù)庫的設(shè)計是基礎(chǔ)的基礎(chǔ)>,一個SQL寫的好不好,就相當(dāng)于我們同樣是要想去英國,但是我的起點在XX,你的起點卻在XX的最邊緣偏僻的一個地方,那不管你做怎樣的最優(yōu)路線選擇,你都不如我在XX去英國所花費的代價小。2,怎么生成的1.Explainplan

explainplanfor

select*fromaa;

查看結(jié)果：

select*fromtable<dbms_xplan.display<>>;

2.AutotraceSettimingon--記錄所用時間

Setautottrace--自動記錄執(zhí)行計劃

3.SQL_TRACE

ORACLESQL_TRACE"SQLTRACE"是Oracle提供的用于進(jìn)行SQL跟蹤的手段,是強有力的輔助診斷工具。在日常的數(shù)據(jù)庫問題診斷和解決中,"SQLTRACE"是非常常用的方法。一般,一次跟蹤可以分為以下幾步：1、界定需要跟蹤的目標(biāo)X圍,并使用適當(dāng)?shù)拿顔⒂盟韪櫋?/p>

2、經(jīng)過一段時間后,停止跟蹤。此時應(yīng)該產(chǎn)生了一個跟蹤結(jié)果文件。

3、找到跟蹤文件,并對其進(jìn)行格式化,然后閱讀或分析。

本文就"SQLTRACE"的這些使用作簡單探討,并通過具體案例對SQL_TRACE的使用進(jìn)行說明。3,怎么查看執(zhí)行計劃從Oracle10g開始,可以通過EXPLAINPLANFOR查看DDL語句的執(zhí)行計劃了。在9i與以前版本,Oracle只能看到DML的執(zhí)行計劃,不過從10g開始,通過EXPLAINPLANFOR的方式,已經(jīng)可以看到DDL語句的執(zhí)行計劃了。這對于研究CREATETABLEASSELECT、CREATEMATERIALIZEDVIEWASSELECT以與CREATEINDEX,ALTERINDEXREBUILD等語句有很大的幫助。舉個簡單的例子,Oracle的文檔上對于索引的建立有如下描述：Theoptimizercanuseanexistingindextobuildanotherindex.Thisresultsinamuchfasterindexbuild.如果看不到DDL的執(zhí)行計劃,只能根據(jù)執(zhí)行時間的長短去猜測Oracle的具體執(zhí)行計劃,但是這種方法沒有足夠的說服力。但是通過DDL的執(zhí)行計劃,就使得結(jié)果一目了然了。SQL>CREATETABLETASSELECT*FROMDBA_OBJECTS;表已創(chuàng)建。SQL>EXPLAINPLANFOR

2CREATEINDEXIND_T_NAMEONT<OBJECT_NAME>;已解釋。SQL>SELECT*FROMTABLE<DBMS_XPLAN.DISPLAY>;PLAN_TABLE_OUTPUT

Planhashvalue:3035241083

|Id|Operation|Name|Rows|Bytes|Cost<%CPU>|Time|

|0|CREATEINDEXSTATEMENT||57915|3732K|75<2>|00:00:01|

|1|INDEXBUILDNONUNIQUE|IND_T_NAME|||||

|2|SORTCREATEINDEX||57915|3732K|||

|3|TABLEACCESSFULL|T|57915|3732K|41<3>|00:00:01|

Note

-estimatedindexsize:5242Kbytes已選擇14行。SQL>CREATEINDEXIND_T_OWNER_NAMEONT<OWNER,OBJECT_NAME>;索引已創(chuàng)建。SQL>EXPLAINPLANFOR

2CREATEINDEXIND_T_NAMEONT<OBJECT_NAME>;已解釋。SQL>SELECT*FROMTABLE<DBMS_XPLAN.DISPLAY>;PLAN_TABLE_OUTPUT

Planhashvalue:517242163

|Id|Operation|Name|Rows|Bytes|Cost<%CPU>|Time|

|0|CREATEINDEXSTATEMENT||57915|3732K|75<2>|00:00:01|

|1|INDEXBUILDNONUNIQUE|IND_T_NAME|||||

|2|SORTCREATEINDEX||57915|3732K|||

|3|INDEXFASTFULLSCAN|IND_T_OWNER_NAME|||||

Note

-estimatedindexsize:5242Kbytes已選擇14行。SQL>SETAUTOTON

SQL>CREATEINDEXIND_T_NAMEONT<OBJECT_NAME>;索引已創(chuàng)建。注意,查看DDL的執(zhí)行計劃需要使用EXPLAINPLANFOR,AUTOTRACE對于DDL是無效的。

4,如何讀懂執(zhí)行計劃：

ExecutionPlan

0SELECTSTATEMENTptimizer=CHOOSE

10SORT<AGGREGATE>

21TABLEACCESS<BYINDEXROWID>OF'USER_INFO'

32NESTEDLOOPS

43TABLEACCESS<FULL>OF'USER_NUM_TABLE'

53INDEX<RANGESCAN>OF'PK_USER_INFO'<UNIQUE>

請問以上執(zhí)行計劃語句是如何看的？語句的執(zhí)行順序是什么？

讓我們來解釋一下怎么看吧,左邊的兩列數(shù)字,第一列表示這條計劃的編號,第二列是這條計劃的父計劃的編號；如果一條計劃有子計劃,那么先要執(zhí)行其子計劃;在這個例子中：從第一條編號為0的〔SELECTSTATEMENTptimizer=CHOOSE〕開始,他有個子計劃1〔SORT<AGGREGATE>〕,然后1有個子計劃2,2有子計劃3,3有子計劃4和5,4是3的第一個子計劃,所以先執(zhí)行4〔TABLEACCESS<FULL>OF'USER_NUM_TABLE'〕,再執(zhí)行5<INDEX<RANGESCAN>OF'PK_USER_INFO'<UNIQUE>>,4和5執(zhí)行完返回到其父計劃3〔NESTEDLOOPS〕,3把4和5取到的rows進(jìn)行nestedloops,結(jié)果再返回到2,再到1排序,再到0select.Oracle執(zhí)行計劃解釋一．相關(guān)的概念

Rowid的概念：rowid是一個偽列,既然是偽列,那么這個列就不是用戶定義,而是系統(tǒng)自己給加上的。對每個表都有一個rowid的偽列,但是表中并不物理存儲ROWID列的值。不過你可以像使用其它列那樣使用它,但是不能刪除改列,也不能對該列的值進(jìn)行修改、插入。一旦一行數(shù)據(jù)插入數(shù)據(jù)庫,則rowid在該行的生命周期內(nèi)是唯一的,即即使該行產(chǎn)生行遷移,行的rowid也不會改變。

RecursiveSQL概念：有時為了執(zhí)行用戶發(fā)出的一個sql語句,Oracle必須執(zhí)行一些額外的語句,我們將這些額外的語句稱之為''recursivecalls''或''recursiveSQLstatements''.如當(dāng)一個DDL語句發(fā)出后,ORACLE總是隱含的發(fā)出一些recursiveSQL語句,來修改數(shù)據(jù)字典信息,以便用戶可以成功的執(zhí)行該DDL語句。當(dāng)需要的數(shù)據(jù)字典信息沒有在共享內(nèi)存中時,經(jīng)常會發(fā)生Recursivecalls,這些Recursivecalls會將數(shù)據(jù)字典信息從硬盤讀入內(nèi)存中。用戶不比關(guān)心這些recursiveSQL語句的執(zhí)行情況,在需要的時候,ORACLE會自動的在內(nèi)部執(zhí)行這些語句。當(dāng)然DML語句與SELECT都可能引起recursiveSQL.簡單的說,我們可以將觸發(fā)器視為recursiveSQL.

RowSource〔行源〕：用在查詢中,由上一操作返回的符合條件的行的集合,即可以是表的全部行數(shù)據(jù)的集合；也可以是表的部分行數(shù)據(jù)的集合；也可以為對上2個rowsource進(jìn)行連接操作〔如join連接〕后得到的行數(shù)據(jù)集合。

Predicate〔謂詞〕：一個查詢中的WHERE限制條件

DrivingTable〔驅(qū)動表〕：該表又稱為外層表〔OUTERTABLE〕。這個概念用于嵌套與HASH連接中。如果該rowsource返回較多的行數(shù)據(jù),則對所有的后續(xù)操作有負(fù)面影響。注意此處雖然翻譯為驅(qū)動表,但實際上翻譯為驅(qū)動行源〔drivingrowsource〕更為確切。一般說來,是應(yīng)用查詢的限制條件后,返回較少行源的表作為驅(qū)動表,所以如果一個大表在WHERE條件有有限制條件〔如等值限制〕,則該大表作為驅(qū)動表也是合適的,所以并不是只有較小的表可以作為驅(qū)動表,正確說法應(yīng)該為應(yīng)用查詢的限制條件后,返回較少行源的表作為驅(qū)動表。在執(zhí)行計劃中,應(yīng)該為靠上的那個rowsource,后面會給出具體說明。在我們后面的描述中,一般將該表稱為連接操作的rowsource1.

ProbedTable〔被探查表〕：該表又稱為內(nèi)層表〔INNERTABLE〕。在我們從驅(qū)動表中得到具體一行的數(shù)據(jù)后,在該表中尋找符合連接條件的行。所以該表應(yīng)當(dāng)為大表〔實際上應(yīng)該為返回較大rowsource的表〕且相應(yīng)的列上應(yīng)該有索引。在我們后面的描述中,一般將該表稱為連接操作的rowsource2.

組合索引〔concatenatedindex〕：由多個列構(gòu)成的索引,如createindexidx_emponemp〔col1,col2,col3,……〕,則我們稱idx_emp索引為組合索引。在組合索引中有一個重要的概念：引導(dǎo)列〔leadingcolumn〕,在上面的例子中,col1列為引導(dǎo)列。當(dāng)我們進(jìn)行查詢時可以使用"wherecol1=？",也可以使用"wherecol1=？andcol2=？",這樣的限制條件都會使用索引,但是"wherecol2=？"查詢就不會使用該索引。所以限制條件中包含先導(dǎo)列時,該限制條件才會使用該組合索引。

可選擇性〔selectivity〕：比較一下列中唯一鍵的數(shù)量和表中的行數(shù),就可以判斷該列的可選擇性。如果該列的"唯一鍵的數(shù)量/表中的行數(shù)"的比值越接近1,則該列的可選擇性越高,該列就越適合創(chuàng)建索引,同樣索引的可選擇性也越高。在可選擇性高的列上進(jìn)行查詢時,返回的數(shù)據(jù)就較少,比較適合使用索引查詢。二．oracle訪問數(shù)據(jù)的存取方法

1〕全表掃描〔FullTableScans,FTS〕

為實現(xiàn)全表掃描,Oracle讀取表中所有的行,并檢查每一行是否滿足語句的WHERE限制條件一個多塊讀操作可以使一次I/O能讀取多塊數(shù)據(jù)塊〔db_block_multiblock_read_count參數(shù)設(shè)定〕,而不是只讀取一個數(shù)據(jù)塊,這極大的減少了I/O總次數(shù),提高了系統(tǒng)的吞吐量,所以利用多塊讀的方法可以十分高效地實現(xiàn)全表掃描,而且只有在全表掃描的情況下才能使用多塊讀操作。在這種訪問模式下,每個數(shù)據(jù)塊只被讀一次。

使用FTS的前提條件：在較大的表上不建議使用全表掃描,除非取出數(shù)據(jù)的比較多,超過總量的5%——10%,或你想使用并行查詢功能時。

使用全表掃描的例子：

~~~~~~~~~~~~~~~~~~~~~~~~SQL>explainplanforselect*fromdual;

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=

TABLEACCESSFULLDUAL

2〕通過ROWID的表存取〔TableAccessbyROWID或rowidlookup〕

行的ROWID指出了該行所在的數(shù)據(jù)文件、數(shù)據(jù)塊以與行在該塊中的位置,所以通過ROWID來存取數(shù)據(jù)可以快速定位到目標(biāo)數(shù)據(jù)上,是Oracle存取單行數(shù)據(jù)的最快方法。

這種存取方法不會用到多塊讀操作,一次I/O只能讀取一個數(shù)據(jù)塊。我們會經(jīng)常在執(zhí)行計劃中看到該存取方法,如通過索引查詢數(shù)據(jù)。

使用ROWID存取的方法：SQL>explainplanforselect*fromdeptwhererowid=''AAAAyGAADAAAAATAAF''；

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDDEPT[ANALYZED]

3〕索引掃描〔IndexScan或indexlookup〕

我們先通過index查找到數(shù)據(jù)對應(yīng)的rowid值〔對于非唯一索引可能返回多個rowid值〕,然后根據(jù)rowid直接從表中得到具體的數(shù)據(jù),這種查找方式稱為索引掃描或索引查找〔indexlookup〕。一個rowid唯一的表示一行數(shù)據(jù),該行對應(yīng)的數(shù)據(jù)塊是通過一次i/o得到的,在此情況下該次i/o只會讀取一個數(shù)據(jù)庫塊。

在索引中,除了存儲每個索引的值外,索引還存儲具有此值的行對應(yīng)的ROWID值。索引掃描可以由2步組成：〔1〕掃描索引得到對應(yīng)的rowid值?！?〕通過找到的rowid從表中讀出具體的數(shù)據(jù)。每步都是單獨的一次I/O,但是對于索引,由于經(jīng)常使用,絕大多數(shù)都已經(jīng)CACHE到內(nèi)存中,所以第1步的I/O經(jīng)常是邏輯I/O,即數(shù)據(jù)可以從內(nèi)存中得到。但是對于第2步來說,如果表比較大,則其數(shù)據(jù)不可能全在內(nèi)存中,所以其I/O很有可能是物理I/O,這是一個機械操作,相對邏輯I/O來說,是極其費時間的。所以如果多大表進(jìn)行索引掃描,取出的數(shù)據(jù)如果大于總量的5%——10%,使用索引掃描會效率下降很多。如下列所示：SQL>explainplanforselectempno,enamefromempwhereempno=10；

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDEMP[ANALYZED]

INDEXUNIQUESCANEMP_I1

但是如果查詢的數(shù)據(jù)能全在索引中找到,就可以避免進(jìn)行第2步操作,避免了不必要的I/O,此時即使通過索引掃描取出的數(shù)據(jù)比較多,效率還是很高的

SQL>explainplanforselectempnofromempwhereempno=10;--只查詢empno列值

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

INDEXUNIQUESCANEMP_I1

進(jìn)一步講,如果sql語句中對索引列進(jìn)行排序,因為索引已經(jīng)預(yù)先排序好了,所以在執(zhí)行計劃中不需要再對索引列進(jìn)行排序

SQL>explainplanforselectempno,enamefromemp

whereempno>7876orderbyempno;

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDEMP[ANALYZED]INDEXRANGE

從這個例子中可以看到：因為索引是已經(jīng)排序了的,所以將按照索引的順序查詢出符合條件的行,因此避免了進(jìn)一步排序操作。

根據(jù)索引的類型與where限制條件的不同,有4種類型的索引掃描：

索引唯一掃描〔indexuniquescan〕

索引X圍掃描〔indexrangescan〕

索引全掃描〔indexfullscan〕

索引快速掃描〔indexfastfullscan〕

〔1〕索引唯一掃描〔indexuniquescan〕

通過唯一索引查找一個數(shù)值經(jīng)常返回單個ROWID.如果存在UNIQUE或PRIMARYKEY約束〔它保證了語句只存取單行〕的話,Oracle經(jīng)常實現(xiàn)唯一性掃描。

使用唯一性約束的例子：

SQL>explainplanfor

selectempno,enamefromempwhereempno=10；

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDEMP[ANALYZED]

INDEXUNIQUESCANEMP_I1

〔2〕索引X圍掃描〔indexrangescan〕

使用一個索引存取多行數(shù)據(jù),在唯一索引上使用索引X圍掃描的典型情況下是在謂詞〔where限制條件〕中使用了X圍操作符〔如>、<、<>、>=、<=、between〕

使用索引X圍掃描的例子：

SQL>explainplanforselectempno,enamefromemp

whereempno>7876orderbyempno；

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDEMP[ANALYZED]INDEXRANGE

在非唯一索引上,謂詞col=5可能返回多行數(shù)據(jù),所以在非唯一索引上都使用索引X圍掃描。

使用indexrangscan的3種情況：

〔a〕在唯一索引列上使用了range操作符〔><<>>=<=between〕

〔b〕在組合索引上,只使用部分列進(jìn)行查詢,導(dǎo)致查詢出多行

〔c〕對非唯一索引列上進(jìn)行的任何查詢。

〔3〕索引全掃描〔indexfullscan〕

與全表掃描對應(yīng),也有相應(yīng)的全索引掃描。而且此時查詢出的數(shù)據(jù)都必須從索引中可以直接得到。

全索引掃描的例子：

AnIndexfullscanwillnotperformsingleblocki/o''sandsoitmayprovetobeinefficient.

e.g.

IndexBE_IXisaconcatenatedindexonbig_emp〔empno,ename〕

SQL>explainplanforselectempno,enamefrombig_emporderbyempno,ename；

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=26

INDEXFULLSCANBE_IX[ANALYZED]

〔4〕索引快速掃描〔indexfastfullscan〕

掃描索引中的所有的數(shù)據(jù)塊,與indexfullscan很類似,但是一個顯著的區(qū)別就是它不對查詢出的數(shù)據(jù)進(jìn)行排序,即數(shù)據(jù)不是以排序順序被返回。在這種存取方法中,可以使用多塊讀功能,也可以使用并行讀入,以便獲得最大吞吐量與縮短執(zhí)行時間。

索引快速掃描的例子：

BE_IX索引是一個多列索引：big_emp〔empno,ename〕

SQL>explainplanforselectempno,enamefrombig_emp；

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

INDEXFASTFULLSCANBE_IX[ANALYZED]

只選擇多列索引的第2列：

SQL>explainplanforselectenamefrombig_emp；

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

INDEXFASTFULLSCANBE_IX[ANALYZED]三、表之間的連接

Join是一種試圖將兩個表結(jié)合在一起的謂詞,一次只能連接2個表,表連接也可以被稱為表關(guān)聯(lián)。在后面的敘述中,我們將會使用"rowsource"來代替"表",因為使用rowsource更嚴(yán)謹(jǐn)一些,并且將參與連接的2個rowsource分別稱為rowsource1和rowsource2.Join過程的各個步驟經(jīng)常是串行操作,即使相關(guān)的rowsource可以被并行訪問,即可以并行的讀取做join連接的兩個rowsource的數(shù)據(jù),但是在將表中符合限制條件的數(shù)據(jù)讀入到內(nèi)存形成rowsource后,join的其它步驟一般是串行的。有多種方法可以將2個表連接起來,當(dāng)然每種方法都有自己的優(yōu)缺點,每種連接類型只有在特定的條件下才會發(fā)揮出其最大優(yōu)勢。

rowsource〔表〕之間的連接順序?qū)τ诓樵兊男视蟹浅４蟮挠绊?。通過首先存取特定的表,即將該表作為驅(qū)動表,這樣可以先應(yīng)用某些限制條件,從而得到一個較小的rowsource,使連接的效率較高,這也就是我們常說的要先執(zhí)行限制條件的原因。一般是在將表讀入內(nèi)存時,應(yīng)用where子句中對該表的限制條件。

根據(jù)2個rowsource的連接條件的中操作符的不同,可以將連接分為等值連接〔如WHEREA.COL3=B.COL4〕、非等值連接〔WHEREA.COL3>B.COL4〕、外連接〔WHEREA.COL3=B.COL4〔+〕〕。上面的各個連接的連接原理都基本一樣,所以為了簡單期間,下面以等值連接為例進(jìn)行介紹。

在后面的介紹中,都已：

SELECTA.COL1,B.COL2

FROMA,B

WHEREA.COL3=B.COL4；

為例進(jìn)行說明,假設(shè)A表為RowSoruce1,則其對應(yīng)的連接操作關(guān)聯(lián)列為COL3；B表為RowSoruce2,則其對應(yīng)的連接操作關(guān)聯(lián)列為COL4；

連接類型：

目前為止,無論連接操作符如何,典型的連接類型共有3種：

排序--合并連接〔SortMergeJoin〔SMJ〕〕

嵌套循環(huán)〔NestedLoops〔NL〕〕

哈希連接〔HashJoin〕

排序--合并連接〔SortMergeJoin,SMJ〕

內(nèi)部連接過程：

1〕首先生成rowsource1需要的數(shù)據(jù),然后對這些數(shù)據(jù)按照連接操作關(guān)聯(lián)列〔如A.col3〕進(jìn)行排序。

2〕隨后生成rowsource2需要的數(shù)據(jù),然后對這些數(shù)據(jù)按照與sortsource1對應(yīng)的連接操作關(guān)聯(lián)列〔如B.col4〕進(jìn)行排序。

3〕最后兩邊已排序的行被放在一起執(zhí)行合并操作,即將2個rowsource按照連接條件連接起來

下面是連接步驟的圖形表示：

MERGE

/\

SORTSORT

||

RowSource1RowSource2

如果rowsource已經(jīng)在連接關(guān)聯(lián)列上被排序,則該連接操作就不需要再進(jìn)行sort操作,這樣可以大大提高這種連接操作的連接速度,因為排序是個極其費資源的操作,特別是對于較大的表。預(yù)先排序的rowsource包括已經(jīng)被索引的列〔如a.col3或b.col4上有索引〕或rowsource已經(jīng)在前面的步驟中被排序了。盡管合并兩個rowsource的過程是串行的,但是可以并行訪問這兩個rowsource〔如并行讀入數(shù)據(jù),并行排序〕。

SMJ連接的例子：SQL>explainplanfor

select/*+ordered*/e.deptno,d.deptno

fromempe,deptd

wheree.deptno=d.deptno

orderbye.deptno,d.deptno；

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=17

MERGEJOIN

SORTJOIN

TABLEACCESSFULLEMP[ANALYZED]

SORTJOIN

TABLEACCESSFULLDEPT[ANALYZED]

排序是一個費時、費資源的操作,特別對于大表?；谶@個原因,SMJ經(jīng)常不是一個特別有效的連接方法,但是如果2個rowsource都已經(jīng)預(yù)先排序,則這種連接方法的效率也是蠻高的。嵌套循環(huán)〔NestedLoops,NL〕

這個連接方法有驅(qū)動表〔外部表〕的概念。其實,該連接過程就是一個2層嵌套循環(huán),所以外層循環(huán)的次數(shù)越少越好,這也就是我們?yōu)槭裁磳⑿”砘蚍祷剌^小rowsource的表作為驅(qū)動表〔用于外層循環(huán)〕的理論依據(jù)。但是這個理論只是一般指導(dǎo)原則,因為遵循這個理論并不能總保證使語句產(chǎn)生的I/O次數(shù)最少。有時不遵守這個理論依據(jù),反而會獲得更好的效率。如果使用這種方法,決定使用哪個表作為驅(qū)動表很重要。有時如果驅(qū)動表選擇不正確,將會導(dǎo)致語句的性能很差、很差。

內(nèi)部連接過程：

Rowsource1的Row1——Probe->Rowsource2

Rowsource1的Row2——Probe->Rowsource2

Rowsource1的Row3——Probe->Rowsource2

……。

Rowsource1的Rown——Probe->Rowsource2

從內(nèi)部連接過程來看,需要用rowsource1中的每一行,去匹配rowsource2中的所有行,所以此時保持rowsource1盡可能的小與高效的訪問rowsource2〔一般通過索引實現(xiàn)〕是影響這個連接效率的關(guān)鍵問題。這只是理論指導(dǎo)原則,目的是使整個連接操作產(chǎn)生最少的物理I/O次數(shù),而且如果遵守這個原則,一般也會使總的物理I/O數(shù)最少。但是如果不遵從這個指導(dǎo)原則,反而能用更少的物理I/O實現(xiàn)連接操作,那盡管違反指導(dǎo)原則吧！因為最少的物理I/O次數(shù)才是我們應(yīng)該遵從的真正的指導(dǎo)原則,在后面的具體案例分析中就給出這樣的例子。

在上面的連接過程中,我們稱Rowsource1為驅(qū)動表或外部表。RowSource2被稱為被探查表或內(nèi)部表。

在NESTEDLOOPS連接中,Oracle讀取rowsource1中的每一行,然后在rowsourc2中檢查是否有匹配的行,所有被匹配的行都被放到結(jié)果集中,然后處理rowsource1中的下一行。這個過程一直繼續(xù),直到rowsource1中的所有行都被處理。這是從連接操作中可以得到第一個匹配行的最快的方法之一,這種類型的連接可以用在需要快速響應(yīng)的語句中,以響應(yīng)速度為主要目標(biāo)。

如果drivingrowsource〔外部表〕比較小,并且在innerrowsource〔內(nèi)部表〕上有唯一索引,或有高選擇性非唯一索引時,使用這種方法可以得到較好的效率。NESTEDLOOPS有其它連接方法沒有的的一個優(yōu)點是：可以先返回已經(jīng)連接的行,而不必等待所有的連接操作處理完才返回數(shù)據(jù),這可以實現(xiàn)快速的響應(yīng)時間。

如果不使用并行操作,最好的驅(qū)動表是那些應(yīng)用了where限制條件后,可以返回較少行數(shù)據(jù)的的表,所以大表也可能稱為驅(qū)動表,關(guān)鍵看限制條件。對于并行查詢,我們經(jīng)常選擇大表作為驅(qū)動表,因為大表可以充分利用并行功能。當(dāng)然,有時對查詢使用并行操作并不一定會比查詢不使用并行操作效率高,因為最后可能每個表只有很少的行符合限制條件,而且還要看你的硬件配置是否可以支持并行〔如是否有多個CPU,多個硬盤控制器〕,所以要具體問題具體對待。

NL連接的例子：

SQL>explainplanfor

selecta.dname,b.sql

fromdepta,empb

wherea.deptno=b.deptno；

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=5

NESTEDLOOPS

TABLEACCESSFULLDEPT[ANALYZED]

TABLEACCESSFULLEMP[ANALYZED]哈希連接〔HashJoin,HJ〕

這種連接是在oracle7.3以后引入的,從理論上來說比NL與SMJ更高效,而且只用在CBO優(yōu)化器中。

較小的rowsource被用來構(gòu)建hasht