ORACLE執(zhí)行計劃.doc

1，什么是執(zhí)行計劃所謂執(zhí)行計劃，顧名思義，就是對一個查詢?nèi)蝿?wù)，做出一份怎樣去完成任務(wù)的詳細方案。舉個生活中的例子，我從珠海要去英國，我可以選擇先去香港然后轉(zhuǎn)機，也可以先去北京轉(zhuǎn)機，或者去廣州也可以。但是到底怎樣去英國劃算，也就是我的費用最少，這是一件值得考究的事情。同樣對于查詢而言，我們提交的SQL僅僅是描述出了我們的目的地是英國，但至于怎么去，通常我們的SQL中是沒有給出提示信息的，是由數(shù)據(jù)庫來決定的。我們先簡單的看一個執(zhí)行計劃的對比：SQL set autotrace traceonly執(zhí)行計劃一：SQL select count(*) from t; COUNT(*) - 24815 Execution Plan 0SELECT STATEMENT Optimizer=CHOOSE 10 SORT (AGGREGATE) 21 TABLE Access (FULL) OF T執(zhí)行計劃二：SQL select count(*) from t; COUNT(*) 24815 Execution Plan 0SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1) 10 SORT (AGGREGATE) 21 INDEX (FULL SCAN) OF T_INDEX (NON-UNIQUE) (Cost=26 Card=28180)這兩個執(zhí)行計劃中，第一個表示求和是通過進行全表掃描來做的，把整個表中數(shù)據(jù)讀入內(nèi)存來逐條累加;第二個表示根據(jù)表中索引，把整個索引讀進內(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 show parameters optimizer_mode NAME TYPEVALUE optimizer_mode string CHOOSE SQL這是Oracle8.1.7 企業(yè)版，我們可以看出，默認安裝后數(shù)據(jù)庫優(yōu)化器模式為CHOOSE,我們還可以設(shè)置為 RULE、FIRST_ROWS,ALL_ROWS?？梢栽趇nit文件中對整個instance的所有會話設(shè)置，也可以單獨對某個會話設(shè)置：SQL ALTER SESSION SET optimizer_mode= RULE; 會話已更改。 SQLALTER SESSION SET optimizer_mode= FIRST_ROWS; 會話已更改。 SQLALTER SESSION SET optimizer_mode= ALL_ROWS; 會話已更改?；谝?guī)則的查詢，數(shù)據(jù)庫根據(jù)表和索引等定義信息，按照一定的規(guī)則來產(chǎn)生執(zhí)行計劃;基于代價的查詢，數(shù)據(jù)庫根據(jù)搜集的表和索引的數(shù)據(jù)的統(tǒng)計信息(通過analyze 命令或者使用dbms_stats包來搜集)綜合來決定選取一個數(shù)據(jù)庫認為最優(yōu)的執(zhí)行計劃(實際上不一定最優(yōu))。RULE是基于規(guī)則的，CHOOSE表示如果查詢的表存在搜集的統(tǒng)計信息則基于代價來執(zhí)行(在CHOOSE模式下Oracle采用的是 FIRST_ROWS)，否則基于規(guī)則來執(zhí)行。在基于代價的兩種方式中，F(xiàn)IRST_ROWS指執(zhí)行計劃采用最少資源盡快的返回部分結(jié)果給客戶端，對于排序分頁頁顯示這種查詢尤其適用，ALL_ROWS指以總體消耗資源最少的方式返回結(jié)果給客戶端?；谝?guī)則的模式下，數(shù)據(jù)庫的執(zhí)行計劃通常比較穩(wěn)定。但在基于代價的模式下，我們才有更大的機會選擇最優(yōu)的執(zhí)行計劃。也由于Oracle的很多查詢方面的特性必須在基于代價的模式下才能體現(xiàn)出來，所以我們通常不選擇RULE(并且Oracle宣稱從 Oracle 10i版本數(shù)據(jù)庫開始將不再支持 RULE)。既然是基于代價的模式，也就是說執(zhí)行計劃的選擇是根據(jù)表、索引等定義和數(shù)據(jù)的統(tǒng)計信息來決定的，這個統(tǒng)計信息是根據(jù) analyze 命令或者dbms_stats包來定期搜集的。首先存在著一種可能，就是由于搜集信息是一個很消耗資源和時間的動作，尤其當表數(shù)據(jù)量很大的時候，因為搜集信息是對整個表數(shù)據(jù)進行重新的完全統(tǒng)計，所以這是我們必須慎重考慮的問題。我們只能在服務(wù)器空閑的時候定期的進行信息搜集。這說明我們在一段時期內(nèi)，統(tǒng)計信息可能和數(shù)據(jù)庫本身的數(shù)據(jù)并不吻合;另外就是Oracle的統(tǒng)計數(shù)據(jù)本身也存在著不精確部分(詳細參考Oracle DOCUMENT)，更重要的一個問題就是及時統(tǒng)計數(shù)據(jù)相對已經(jīng)比較準確，但是Oracle的優(yōu)化器的選擇也并不是始終是最優(yōu)的方案。這也倚賴于Oracle對不同執(zhí)行計劃的代價的計算規(guī)則(我們通常是無法知道具體的計算規(guī)則的)。這好比我們決定從香港還是從北京去英國，車票、機票等實際價格到底是怎么核算出來的我們并不知道，或者說我們現(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)化器目標(RULE、CHOOSE、FIRST_ROWS、ALL_ROWS)，可以指定表連接的順序，可以指定使用哪個表的哪個索引等等，可以對SQL進行很多精細的控制。通過這種方式產(chǎn)生我們想要的執(zhí)行計劃的這些HINTS,Oracle可以存儲這些HINTS，我們稱之為OUTLINES。通過STORE OUTLINES可以使得我們擁有以后產(chǎn)生相同執(zhí)行計劃的能力，也就是使我們擁有了穩(wěn)定執(zhí)行計劃的能力。這里想給出一個附加的說明就是，實際上，我們通過工具改寫SQL，比如使用SQLEXPERT改寫后的SQL，這些不僅僅是加了HINTS而且文本都已經(jīng)發(fā)生了變化的SQL，也可以存儲OUTLINES，并可被應(yīng)用到應(yīng)用中。但這不是一定生效，我們必須測試檢查是否生效。但由于就算給了錯誤的OUTLINES,數(shù)據(jù)庫在執(zhí)行的時候，也只是忽略過去重新生成執(zhí)行計劃而不會返回錯誤，所以我們才敢放心的這么使用。當然在Oracle文檔中并沒有指明可以這樣做，文檔中只是說明，如果存在OUTLINES的同時又在SQL中加了HINTS，則會使用OUTLINES而忽略HINTS。這個功能在LECCO將發(fā)布的產(chǎn)品中會使用這一功能，這樣可以將SQL EXPERT的改寫SQL的能力和穩(wěn)定執(zhí)行計劃的能力結(jié)合起來，那么我們就對不能更改源代碼的應(yīng)用具有了相當強大的SQL優(yōu)化能力。也許我們會有疑問，假如穩(wěn)定了執(zhí)行計劃，那還搜集統(tǒng)計信息干嗎?這是因為幾個原因造成的，首先，現(xiàn)在的執(zhí)行計劃對于未來發(fā)生了變化的數(shù)據(jù)未必就是合適的，存在著當前的執(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ā)生變化(DB EXPERT的Plan Version Tracer是模擬不同環(huán)境并自動檢查不同環(huán)境中執(zhí)行計劃變化的工具)，是變好了還是變差了。我們可以把變差的這一部分在測試環(huán)境中使用hints或者利用工具(SQL EXPERT是在重寫SQL這一領(lǐng)域目前最強有力的工具)產(chǎn)生良好的執(zhí)行計劃的SQL，利用這些SQL可以產(chǎn)生OUTLINES,然后在產(chǎn)品數(shù)據(jù)庫應(yīng)用最新的統(tǒng)計信息的同時移植進這些OUTLINES。最后說一下我們不得不使用執(zhí)行計劃穩(wěn)定性能力的場合。我們假定Oracle的優(yōu)化器的選擇都是準確的，但是優(yōu)化器選擇的基礎(chǔ)就是我們的SQL,這些SQL才從根本上決定了運行效率，這是更重要的一個優(yōu)化的環(huán)節(jié)。SQL是基礎(chǔ)(當然數(shù)據(jù)庫的設(shè)計是基礎(chǔ)的基礎(chǔ))，一個SQL寫的好不好，就相當于我們同樣是要想去英國，但是我的起點在珠海，你的起點卻在西藏的最邊緣偏僻的一個地方，那不管你做怎樣的最優(yōu)路線選擇，你都不如我在珠海去英國所花費的代價小。2，怎么生成的1.Explain planexplain plan for select * from aa;查看結(jié)果：select * from table(dbms_xplan.display(); 2.Autotrace Set timing on -記錄所用時間Set autot trace -自動記錄執(zhí)行計劃3.SQL_TRACEORACLE SQL_TRACE“SQL TRACE”是Oracle提供的用于進行SQL跟蹤的手段，是強有力的輔助診斷工具。在日常的數(shù)據(jù)庫問題診斷和解決中，“SQL TRACE”是非常常用的方法。一般，一次跟蹤可以分為以下幾步：1、界定需要跟蹤的目標范圍，并使用適當?shù)拿顔⒂盟韪櫋?、經(jīng)過一段時間后，停止跟蹤。此時應(yīng)該產(chǎn)生了一個跟蹤結(jié)果文件。3、找到跟蹤文件，并對其進行格式化，然后閱讀或分析。本文就“SQL TRACE”的這些使用作簡單探討，并通過具體案例對SQL_TRACE的使用進行說明。3，怎么查看執(zhí)行計劃從Oracle10g開始，可以通過EXPLAIN PLAN FOR查看DDL語句的執(zhí)行計劃了。在9i及以前版本，Oracle只能看到DML的執(zhí)行計劃，不過從10g開始，通過EXPLAIN PLAN FOR的方式，已經(jīng)可以看到DDL語句的執(zhí)行計劃了。這對于研究CREATE TABLE AS SELECT、CREATE MATERIALIZED VIEW AS SELECT以及CREATE INDEX，ALTER INDEX REBUILD等語句有很大的幫助。舉個簡單的例子，Oracle的文檔上對于索引的建立有如下描述：The optimizer can use an existing index to build another index. This results in a much faster index build.如果看不到DDL的執(zhí)行計劃，只能根據(jù)執(zhí)行時間的長短去猜測Oracle的具體執(zhí)行計劃，但是這種方法沒有足夠的說服力。但是通過DDL的執(zhí)行計劃，就使得結(jié)果一目了然了。SQL CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;表已創(chuàng)建。SQL EXPLAIN PLAN FOR2 CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);已解釋。SQL SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT-Plan hash value: 3035241083-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | CREATE INDEX STATEMENT | | 57915 | 3732K| 75 (2)| 00:00:01 | 1 | INDEX BUILD NON UNIQUE| IND_T_NAME | | | | | 2 | SORT CREATE INDEX | | 57915 | 3732K| | | 3 | TABLE ACCESS FULL | T | 57915 | 3732K| 41 (3)| 00:00:01 |-Note- estimated index size: 5242K bytes已選擇14行。SQL CREATE INDEX IND_T_OWNER_NAME ON T(OWNER, OBJECT_NAME);索引已創(chuàng)建。SQL EXPLAIN PLAN FOR2 CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);已解釋。SQL SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT-Plan hash value: 517242163-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | CREATE INDEX STATEMENT | | 57915 | 3732K| 75 (2)| 00:00:01 | 1 | INDEX BUILD NON UNIQUE| IND_T_NAME | | | | | 2 | SORT CREATE INDEX | | 57915 | 3732K| | | 3 | INDEX FAST FULL SCAN| IND_T_OWNER_NAME | | | | |-Note- estimated index size: 5242K bytes已選擇14行。SQL SET AUTOT ONSQL CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);索引已創(chuàng)建。注意，查看DDL的執(zhí)行計劃需要使用EXPLAIN PLAN FOR，AUTOTRACE對于DDL是無效的。4，如何讀懂執(zhí)行計劃：Execution Plan-0 SELECT STATEMENT ptimizer=CHOOSE1 0 SORT (AGGREGATE)2 1 TABLE ACCESS (BY INDEX ROWID) OF USER_INFO3 2 NESTED LOOPS4 3 TABLE ACCESS (FULL) OF USER_NUM_TABLE5 3 INDEX (RANGE SCAN) OF PK_USER_INFO (UNIQUE)請問以上執(zhí)行計劃語句是如何看的？語句的執(zhí)行順序是什么？ 讓我們來解釋一下怎么看吧,左邊的兩列數(shù)字，第一列表示這條計劃的編號，第二列是這條計劃的父計劃的編號；如果一條計劃有子計劃，那么先要執(zhí)行其子計劃;在這個例子中：從第一條編號為0的（SELECT STATEMENT ptimizer=CHOOSE）開始，他有個子計劃1（SORT (AGGREGATE)），然后1有個子計劃2，2有子計劃3， 3 有子計劃4和5，4是3的第一個子計劃，所以先執(zhí)行4（TABLE ACCESS (FULL) OF USER_NUM_TABLE），再執(zhí)行5(INDEX (RANGE SCAN) OF PK_USER_INFO (UNIQUE)，4和5執(zhí)行完返回到其父計劃3（NESTED LOOPS），3把4和5取到的rows進行nested loops，結(jié)果再返回到2，再到1排序，再到0select.Oracle執(zhí)行計劃解釋一相關(guān)的概念 Rowid的概念：rowid是一個偽列，既然是偽列，那么這個列就不是用戶定義，而是系統(tǒng)自己給加上的。 對每個表都有一個rowid的偽列，但是表中并不物理存儲ROWID列的值。不過你可以像使用其它列那樣使用它，但是不能刪除改列，也不能對該列的值進行 修改、插入。一旦一行數(shù)據(jù)插入數(shù)據(jù)庫，則rowid在該行的生命周期內(nèi)是唯一的，即即使該行產(chǎn)生行遷移，行的rowid也不會改變。 Recursive SQL概念：有時為了執(zhí)行用戶發(fā)出的一個sql語句，Oracle必須執(zhí)行一些額外的語句，我們將這些額外的語句稱之為recursive calls或recursive SQL statements.如當一個DDL語句發(fā)出后，ORACLE總是隱含的發(fā)出一些recursive SQL語句，來修改數(shù)據(jù)字典信息，以便用戶可以成功的執(zhí)行該DDL語句。當需要的數(shù)據(jù)字典信息沒有在共享內(nèi)存中時，經(jīng)常會發(fā)生Recursive calls，這些Recursive calls會將數(shù)據(jù)字典信息從硬盤讀入內(nèi)存中。用戶不比關(guān)心這些recursive SQL語句的執(zhí)行情況，在需要的時候，ORACLE會自動的在內(nèi)部執(zhí)行這些語句。當然DML語句與SELECT都可能引起recursive SQL.簡單的說，我們可以將觸發(fā)器視為recursive SQL. Row Source（行源）：用在查詢中，由上一操作返回的符合條件的行的集合，即可以是表的全部行數(shù)據(jù)的集合；也可以是表的部分行數(shù)據(jù)的集合；也可以為對上2個row source進行連接操作（如join連接）后得到的行數(shù)據(jù)集合。 Predicate（謂詞）：一個查詢中的WHERE限制條件 Driving Table（驅(qū)動表）：該表又稱為外層表（OUTER TABLE）。這個概念用于嵌套與HASH連接中。如果該row source返回較多的行數(shù)據(jù)，則對所有的后續(xù)操作有負面影響。注意此處雖然翻譯為驅(qū)動表，但實際上翻譯為驅(qū)動行源（driving row source）更為確切。一般說來，是應(yīng)用查詢的限制條件后，返回較少行源的表作為驅(qū)動表，所以如果一個大表在WHERE條件有有限制條件（如等值限 制），則該大表作為驅(qū)動表也是合適的，所以并不是只有較小的表可以作為驅(qū)動表，正確說法應(yīng)該為應(yīng)用查詢的限制條件后，返回較少行源的表作為驅(qū)動表。在執(zhí)行 計劃中，應(yīng)該為靠上的那個row source，后面會給出具體說明。在我們后面的描述中，一般將該表稱為連接操作的row source 1. Probed Table（被探查表）：該表又稱為內(nèi)層表（INNER TABLE）。在我們從驅(qū)動表中得到具體一行的數(shù)據(jù)后，在該表中尋找符合連接條件的行。所以該表應(yīng)當為大表（實際上應(yīng)該為返回較大row source的表）且相應(yīng)的列上應(yīng)該有索引。在我們后面的描述中，一般將該表稱為連接操作的row source 2. 組合索引（concatenated index）：由多個列構(gòu)成的索引，如create index idx_emp on emp（col1， col2， col3， ），則我們稱idx_emp索引為組合索引。在組合索引中有一個重要的概念：引導(dǎo)列（leading column），在上面的例子中，col1列為引導(dǎo)列。當我們進行查詢時可以使用“where col1 = ？ ”，也可以使用“where col1 = ？ and col2 = ？”，這樣的限制條件都會使用索引，但是“where col2 = ？ ”查詢就不會使用該索引。所以限制條件中包含先導(dǎo)列時，該限制條件才會使用該組合索引。 可選擇性（selectivity）：比較一下列中唯一鍵的數(shù)量和表中的行數(shù)，就可以判斷該列的可選擇性。 如果該列的“唯一鍵的數(shù)量/表中的行數(shù)”的比值越接近1，則該列的可選擇性越高，該列就越適合創(chuàng)建索引，同樣索引的可選擇性也越高。在可選擇性高的列上進 行查詢時，返回的數(shù)據(jù)就較少，比較適合使用索引查詢。二oracle訪問數(shù)據(jù)的存取方法 1） 全表掃描（Full Table Scans， 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 explain plan for select * from dual; Query Plan - SELECT STATEMENTCHOOSE Cost= TABLE ACCESS FULL DUAL 2） 通過ROWID的表存取（Table Access by ROWID或rowid lookup） 行的ROWID指出了該行所在的數(shù)據(jù)文件、數(shù)據(jù)塊以及行在該塊中的位置，所以通過ROWID來存取數(shù)據(jù)可以快速定位到目標數(shù)據(jù)上，是Oracle存取單行數(shù)據(jù)的最快方法。 這種存取方法不會用到多塊讀操作，一次I/O只能讀取一個數(shù)據(jù)塊。我們會經(jīng)常在執(zhí)行計劃中看到該存取方法，如通過索引查詢數(shù)據(jù)。 使用ROWID存取的方法： SQL explain plan for select * from dept where rowid = AAAAyGAADAAAAATAAF； Query Plan - SELECT STATEMENT CHOOSE Cost=1 TABLE ACCESS BY ROWID DEPT ANALYZED 3）索引掃描（Index Scan或index lookup） 我們先通過index查找到數(shù)據(jù)對應(yīng)的rowid值（對于非唯一索引可能返回多個rowid值），然后根據(jù)rowid直接從表中得到具體的數(shù)據(jù)，這 種查找方式稱為索引掃描或索引查找（index lookup）。一個rowid唯一的表示一行數(shù)據(jù)，該行對應(yīng)的數(shù)據(jù)塊是通過一次i/o得到的，在此情況下該次i/o只會讀取一個數(shù)據(jù)庫塊。 在索引中，除了存儲每個索引的值外，索引還存儲具有此值的行對應(yīng)的ROWID值。索引掃描可以由2步組成：（1） 掃描索引得到對應(yīng)的rowid值。 （2） 通過找到的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來說，是極其費時間的。所以如果多大表進行索引掃描，取出的數(shù)據(jù)如果大于總量的5% 10%，使用索引掃描會效率下降很多。如下列所示：SQL explain plan for select empno， ename from emp where empno=10； Query Plan - SELECT STATEMENT CHOOSE Cost=1 TABLE ACCESS BY ROWID EMP ANALYZED INDEX UNIQUE SCAN EMP_I1 但是如果查詢的數(shù)據(jù)能全在索引中找到，就可以避免進行第2步操作，避免了不必要的I/O，此時即使通過索引掃描取出的數(shù)據(jù)比較多，效率還是很高的 SQL explain plan for select empno from emp where empno=10;- 只查詢empno列值 Query Plan - SELECT STATEMENT CHOOSE Cost=1 INDEX UNIQUE SCAN EMP_I1 進一步講，如果sql語句中對索引列進行排序，因為索引已經(jīng)預(yù)先排序好了，所以在執(zhí)行計劃中不需要再對索引列進行排序 SQL explain plan for select empno, ename from emp where empno 7876 order by empno; Query Plan - SELECT STATEMENTCHOOSE Cost=1 TABLE ACCESS BY ROWID EMP ANALYZED INDEX RANGE SCAN EMP_I1 ANALYZED 從這個例子中可以看到：因為索引是已經(jīng)排序了的，所以將按照索引的順序查詢出符合條件的行，因此避免了進一步排序操作。 根據(jù)索引的類型與where限制條件的不同，有4種類型的索引掃描： 索引唯一掃描（index unique scan） 索引范圍掃描（index range scan） 索引全掃描（index full scan） 索引快速掃描（index fast full scan） （1） 索引唯一掃描（index unique scan） 通過唯一索引查找一個數(shù)值經(jīng)常返回單個ROWID.如果存在UNIQUE 或PRIMARY KEY 約束（它保證了語句只存取單行）的話，Oracle經(jīng)常實現(xiàn)唯一性掃描。 使用唯一性約束的例子： SQL explain plan for select empno，ename from emp where empno=10； Query Plan- SELECT STATEMENT CHOOSE Cost=1 TABLE ACCESS BY ROWID EMP ANALYZED INDEX UNIQUE SCAN EMP_I1 （2） 索引范圍掃描（index range scan） 使用一個索引存取多行數(shù)據(jù)，在唯一索引上使用索引范圍掃描的典型情況下是在謂詞（where限制條件）中使用了范圍操作符（如、=、 explain plan for select empno，ename from emp where empno 7876 order by empno； Query Plan- SELECT STATEMENTCHOOSE Cost=1 TABLE ACCESS BY ROWID EMP ANALYZED INDEX RANGE SCAN EMP_I1 ANALYZED 在非唯一索引上，謂詞col = 5可能返回多行數(shù)據(jù)，所以在非唯一索引上都使用索引范圍掃描。 使用index rang scan的3種情況： （a） 在唯一索引列上使用了range操作符（ = explain plan for select empno， ename from big_emp order by empno，ename； Query Plan- SELECT STATEMENTCHOOSE Cost=26 INDEX FULL SCAN BE_IX ANALYZED （4） 索引快速掃描（index fast full scan） 掃描索引中的所有的數(shù)據(jù)塊，與 index full scan很類似，但是一個顯著的區(qū)別就是它不對查詢出的數(shù)據(jù)進行排序，即數(shù)據(jù)不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執(zhí)行時間。 索引快速掃描的例子： BE_IX索引是一個多列索引： big_emp （empno，ename） SQL explain plan for select empno，ename from big_emp； Query Plan- SELECT STATEMENTCHOOSE Cost=1 INDEX FAST FULL SCAN BE_IX ANALYZED 只選擇多列索引的第2列： SQL explain plan for select ename from big_emp； Query Plan- SELECT STATEMENTCHOOSE Cost=1 INDEX FAST FULL SCAN BE_IX ANALYZED三、表之間的連接 Join是一種試圖將兩個表結(jié)合在一起的謂詞，一次只能連接2個表，表連接也可以被稱為表關(guān)聯(lián)。在后面的敘 述中，我們將會使用“row source”來代替“表”，因為使用row source更嚴謹一些，并且將參與連接的2個row source分別稱為row source1和row source 2.Join過程的各個步驟經(jīng)常是串行操作，即使相關(guān)的row source可以被并行訪問，即可以并行的讀取做join連接的兩個row source的數(shù)據(jù)，但是在將表中符合限制條件的數(shù)據(jù)讀入到內(nèi)存形成row source后，join的其它步驟一般是串行的。有多種方法可以將2個表連接起來，當然每種方法都有自己的優(yōu)缺點，每種連接類型只有在特定的條件下才會 發(fā)揮出其最大優(yōu)勢。 row source（表）之間的連接順序?qū)τ诓樵兊男视蟹浅４蟮挠绊?。通過首先存取特定的表，即將該表作為驅(qū)動表，這樣可以先應(yīng)用某些限制條件，從而得到一個 較小的row source，使連接的效率較高，這也就是我們常說的要先執(zhí)行限制條件的原因。一般是在將表讀入內(nèi)存時，應(yīng)用where子句中對該表的限制條件。 根據(jù)2個row source的連接條件的中操作符的不同，可以將連接分為等值連接（如WHERE A.COL3 = B.COL4）、非等值連接（WHERE A.COL3 B.COL4）、外連接（WHERE A.COL3 = B.COL4（+）。上面的各個連接的連接原理都基本一樣，所以為了簡單期間，下面以等值連接為例進行介紹。 在后面的介紹中，都已： SELECT A.COL1， B.COL2 FROM A， B WHERE A.COL3 = B.COL4； 為例進行說明，假設(shè)A表為Row Soruce1，則其對應(yīng)的連接操作關(guān)聯(lián)列為COL 3；B表為Row Soruce2，則其對應(yīng)的連接操作關(guān)聯(lián)列為COL 4； 連接類型： 目前為止，無論連接操作符如何，典型的連接類型共有3種： 排序 - - 合并連接（Sort Merge Join （SMJ） ） 嵌套循環(huán)（Nested Loops （NL） ） 哈希連接（Hash Join） 排序 - - 合并連接（Sort Merge Join， SMJ） 內(nèi)部連接過程： 1） 首先生成row source1需要的數(shù)據(jù)，然后對這些數(shù)據(jù)按照連接操作關(guān)聯(lián)列（如A.col3）進行排序。 2） 隨后生成row source2需要的數(shù)據(jù)，然后對這些數(shù)據(jù)按照與sort source1對應(yīng)的連接操作關(guān)聯(lián)列（如B.col4）進行排序。 3） 最后兩邊已排序的行被放在一起執(zhí)行合并操作，即將2個row source按照連接條件連接起來 下面是連接步驟的圖形表示： MERGE / SORTSORT | Row Source 1Row Source 2 如果row source已經(jīng)在連接關(guān)聯(lián)列上被排序，則該連接操作就不需要再進行sort操作，這樣可以大大提高這種連接操作的連接速度，因為排序是個極其費資源的操 作，特別是對于較大的表。預(yù)先排序的row source包括已經(jīng)被索引的列（如a.col3或b.col4上有索引）或row source已經(jīng)在前面的步驟中被排序了。盡管合并兩個row source的過程是串行的，但是可以并行訪問這兩個row source（如并行讀入數(shù)據(jù)，并行排序）。 SMJ連接的例子：SQL explain plan for select /*+ ordered */ e.deptno， d.deptno from emp e， dept d where e.deptno = d.deptno order by e.deptno， d.deptno； Query Plan- SELECT STATEMENT CHOOSE Cost=17 MERGE JOIN SORT JOIN TABLE ACCESS FULL EMP ANALYZED SORT JOIN TABLE ACCESS FULL DEPT ANALYZED 排序是一個費時、費資源的操作，特別對于大表。基于這個原因，SMJ經(jīng)常不是一個特別有效的連接方法，但是如果2個row source都已經(jīng)預(yù)先排序，則這種連接方法的效率也是蠻高的。 嵌套循環(huán)（Nested Loops， NL） 這個連接方法有驅(qū)動表（外部表）的概念。其實，該連接過程就是一個2層嵌套循環(huán)，所以外層循環(huán)的次數(shù)越少越好，這也就是我們?yōu)槭裁磳⑿”砘蚍祷剌^小 row source的表作為驅(qū)動表（用于外層循環(huán)）的理論依據(jù)。但是這個理論只是一般指導(dǎo)原則，因為遵循這個理論并不能總保證使語句產(chǎn)生的I/O次數(shù)最少。有時 不遵守這個理論依據(jù)，反而會獲得更好的效率。如果使用這種方法，決定使用哪個表作為驅(qū)動表很重要。有時如果驅(qū)動表選擇不正確，將會導(dǎo)致語句的性能很差、很差。 內(nèi)部連接過程： Row source1的Row 1 Probe -Row source 2 Row source1的Row 2 Probe -Row source 2 Row source1的Row 3 Probe -Row source 2 。 Row source1的Row n Probe -Row source 2 從內(nèi)部連接過程來看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此時保持row source1盡可能的小與高效的訪問row source2（一般通過索引實現(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)原則，在后面的具體案例分析中就給出這樣的例子。 在上面的連接過程中，我們稱Row source1為驅(qū)動表或外部表。Row Source2被稱為被探查表或內(nèi)部表。 在NESTED LOOPS連接中，Oracle讀取row source1中的每一行，然后在row sourc2中檢查是否有匹配的行，所有被匹配的行都被放到結(jié)果集中，然后處理row source1中的下一行。這個過程一直繼續(xù)，直到row source1中的所有行都被處理。這是從連接操作中可以得到第一個匹配行的最快的方法之一，這種類型的連接可以用在需要快速響應(yīng)的語句中，以響應(yīng)速度為 主要目標。 如果driving row source（外部表）比較小，并且在inner row source（內(nèi)部表）上有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。NESTED LOOPS有其它連接方法沒有的的一個優(yōu)點是：可以先返回已經(jīng)連接的行，而不必等待所有的連接操作處理完才返回數(shù)據(jù)，這可以實現(xiàn)快速的響應(yīng)時間。 如果不使用并行操作，最好的驅(qū)動表是那些應(yīng)用了where 限制條件后，可以返回較少行數(shù)據(jù)的的表，所以大表也可能稱為驅(qū)動表，關(guān)鍵看限制條件。對于并行查詢，我們經(jīng)常選擇大表作為驅(qū)動表，因為大表可以充分利用并 行功能。當然，有時對查詢使用并行操作并不一定會比查詢不使用并行操作效率高，因為最后可能每個表只有很少的行符合限制條件，而且還要看你的硬件配置是否 可以支持并行（如是否有多個CPU，多個硬盤控制器），所以要具體問題具體對待。 NL連接的例子： SQL explain plan for select a.dn