ORACLE中的執(zhí)行計(jì)劃

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

COUNT<*>

24815

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10SORT<AGGREGATE>

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

COUNT<*>

24815

ExecutionPlan

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

10SORT<AGGREGATE>

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

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

NAMETYPEVALUE

optimizer_modestringCHOOSE

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

會(huì)話已更改。

SQL>ALTERSESSIONSEToptimizer_mode=FIRST_ROWS;

會(huì)話已更改。

SQL>ALTERSESSIONSEToptimizer_mode=ALL_ROWS;

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

explainplanfor

select*fromaa;

查看結(jié)果：

select*fromtable<dbms_xplan.display<>>;

2.AutotraceSettimingon--記錄所用時(shí)間

Setautottrace--自動(dòng)記錄執(zhí)行計(jì)劃

3.SQL_TRACE

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

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

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

本文就"SQLTRACE"的這些使用作簡(jiǎn)單探討,并通過具體案例對(duì)SQL_TRACE的使用進(jìn)行說明。3,怎么查看執(zhí)行計(jì)劃從Oracle10g開始,可以通過EXPLAINPLANFOR查看DDL語(yǔ)句的執(zhí)行計(jì)劃了。在9i與以前版本,Oracle只能看到DML的執(zhí)行計(jì)劃,不過從10g開始,通過EXPLAINPLANFOR的方式,已經(jīng)可以看到DDL語(yǔ)句的執(zhí)行計(jì)劃了。這對(duì)于研究CREATETABLEASSELECT、CREATEMATERIALIZEDVIEWASSELECT以與CREATEINDEX,ALTERINDEXREBUILD等語(yǔ)句有很大的幫助。舉個(gè)簡(jiǎn)單的例子,Oracle的文檔上對(duì)于索引的建立有如下描述：Theoptimizercanuseanexistingindextobuildanotherindex.Thisresultsinamuchfasterindexbuild.如果看不到DDL的執(zhí)行計(jì)劃,只能根據(jù)執(zhí)行時(shí)間的長(zhǎng)短去猜測(cè)Oracle的具體執(zhí)行計(jì)劃,但是這種方法沒有足夠的說服力。但是通過DDL的執(zhí)行計(jì)劃,就使得結(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í)行計(jì)劃需要使用EXPLAINPLANFOR,AUTOTRACE對(duì)于DDL是無(wú)效的。

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

ExecutionPlan

0SELECTSTATEMENTptimizer=CHOOSE

10SORT<AGGREGATE>

21TABLEACCESS<BYINDEXROWID>OF'USER_INFO'

32NESTEDLOOPS

43TABLEACCESS<FULL>OF'USER_NUM_TABLE'

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

請(qǐng)問以上執(zhí)行計(jì)劃語(yǔ)句是如何看的？語(yǔ)句的執(zhí)行順序是什么？

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

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

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

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

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

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

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

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

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

1〕全表掃描〔FullTableScans,FTS〕

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

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

使用全表掃描的例子：

~~~~~~~~~~~~~~~~~~~~~~~~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ù)的最快方法。

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

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

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDDEPT[ANALYZED]

3〕索引掃描〔IndexScan或indexlookup〕

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

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

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDEMP[ANALYZED]

INDEXUNIQUESCANEMP_I1

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

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

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

INDEXUNIQUESCANEMP_I1

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

SQL>explainplanforselectempno,enamefromemp

whereempno>7876orderbyempno;

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDEMP[ANALYZED]INDEXRANGE

從這個(gè)例子中可以看到：因?yàn)樗饕且呀?jīng)排序了的,所以將按照索引的順序查詢出符合條件的行,因此避免了進(jìn)一步排序操作。

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

索引唯一掃描〔indexuniquescan〕

索引X圍掃描〔indexrangescan〕

索引全掃描〔indexfullscan〕

索引快速掃描〔indexfastfullscan〕

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

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

使用唯一性約束的例子：

SQL>explainplanfor

selectempno,enamefromempwhereempno=10；

QueryPlan

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDEMP[ANALYZED]

INDEXUNIQUESCANEMP_I1

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

使用一個(gè)索引存取多行數(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〕對(duì)非唯一索引列上進(jìn)行的任何查詢。

〔3〕索引全掃描〔indexfullscan〕

與全表掃描對(duì)應(yīng),也有相應(yīng)的全索引掃描。而且此時(shí)查詢出的數(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很類似,但是一個(gè)顯著的區(qū)別就是它不對(duì)查詢出的數(shù)據(jù)進(jìn)行排序,即數(shù)據(jù)不是以排序順序被返回。在這種存取方法中,可以使用多塊讀功能,也可以使用并行讀入,以便獲得最大吞吐量與縮短執(zhí)行時(shí)間。

索引快速掃描的例子：

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

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

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

在后面的介紹中,都已：

SELECTA.COL1,B.COL2

FROMA,B

WHEREA.COL3=B.COL4；

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

連接類型：

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

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

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

哈希連接〔HashJoin〕

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

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

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

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

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

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

MERGE

/\

SORTSORT

||

RowSource1RowSource2

如果rowsource已經(jīng)在連接關(guān)聯(lián)列上被排序,則該連接操作就不需要再進(jìn)行sort操作,這樣可以大大提高這種連接操作的連接速度,因?yàn)榕判蚴莻€(gè)極其費(fèi)資源的操作,特別是對(duì)于較大的表。預(yù)先排序的rowsource包括已經(jīng)被索引的列〔如a.col3或b.col4上有索引〕或rowsource已經(jīng)在前面的步驟中被排序了。盡管合并兩個(gè)rowsource的過程是串行的,但是可以并行訪問這兩個(gè)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]

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

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

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

Rowsource1的Row1——Probe->Rowsource2

Rowsource1的Row2——Probe->Rowsource2

Rowsource1的Row3——Probe->Rowsource2

……。

Rowsource1的Rown——Probe->Rowsource2

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

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

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

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

如果不使用并行操作,最好的驅(qū)動(dòng)表是那些應(yīng)用了where限制條件后,可以返回較少行數(shù)據(jù)的的表,所以大表也可能稱為驅(qū)動(dòng)表,關(guān)鍵看限制條件。對(duì)于并行查詢,我們經(jīng)常選擇大表作為驅(qū)動(dòng)表,因?yàn)榇蟊砜梢猿浞掷貌⑿泄δ堋．?dāng)然,有時(shí)對(duì)查詢使用并行操作并不一定會(huì)比查詢不使用并行操作效率高,因?yàn)樽詈罂赡苊總€(gè)表只有很少的行符合限制條件,而且還要看你的硬件配置是否可以支持并行〔如是否有多個(gè)CPU,多個(gè)硬盤控制器〕,所以要具體問題具體對(duì)待。

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