【正文】 ALYZED] 在非唯一索引上，謂詞col = 5可能返回多行數(shù)據(jù)，所以在非唯一索引上都使用索引范圍掃描。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執(zhí)行時間。通過首先存取特定的表，即將該表作為驅(qū)動表，這樣可以先應(yīng)用某些限制條件，從而得到一個較小的row source，使連接的效率較高，這也就是我們常說的要先執(zhí)行限制條件的原因。 預(yù)先排序的row source包括已經(jīng)被索引的列()或row source已經(jīng)在前面的步驟中被排序了。如果使用這種方法，決定使用哪個表作為驅(qū)動表很重要。這個過程一直繼續(xù)，直到row source1中的所有行都被處理。 Query Plan SELECT STATEMENT [CHOOSE] Cost=5 NESTED LOOPS TABLE ACCESS FULL DEPT [ANALYZED] TABLE ACCESS FULL EMP [ANALYZED] 哈希連接(Hash Join, HJ) 這種連接是在oracle ，從理論上來說比NL與SMJ更高效，而且只用在CBO優(yōu)化器中。 Query Plan SELECT STATEMENT [CHOOSE] Cost=3 HASH JOIN TABLE ACCESS FULL DEPT TABLE ACCESS FULL EMP 要使哈希連接有效，需要設(shè)置HASH_JOIN_ENABLED=TRUE，缺省情況下該參數(shù)為TRUE，另外，不要忘了還要設(shè)置hash_area_size參數(shù)，以使哈希連接高效運行，因為哈希連接會在該參數(shù)指定大小的內(nèi)存中運行，過小的參數(shù)會使哈希連接的性能比其他連接方式還要低。當(dāng)然，有時對查詢使用并行操作并不一定會比查詢不使用并行操作效率高，因為最后可能每個表只有很少的行符合限制條件，而且還要看你的硬件配置是否可以支持并行(如是否有多個CPU，多個硬盤控制器)，所以要具體問題具體對待。Row Source2被稱為被探查表或內(nèi)部表。但是這個理論只是一般指導(dǎo)原則，因為遵循這個理論并不能總保證使語句產(chǎn)生的I/O次數(shù)最少。 2) 隨后生成row source2需要的數(shù)據(jù)，然后對這些數(shù)據(jù)按照與sort source1對應(yīng)的連接操作關(guān)聯(lián)列()進行排序。有多種方法可以將2個表連接起來，當(dāng)然每種方法都有自己的優(yōu)缺點，每種連接類型只有在特定的條件下才會發(fā)揮出其最大優(yōu)勢。s and so it may prove to be inefficient. . Index BE_IX is a concatenated index on big_emp (empno, ename) SQL explain plan for select empno, ename from big_emp order by empno,ename。 Query Plan SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I1 (2) 索引范圍掃描(index range scan) 使用一個索引存取多行數(shù)據(jù)，同上面一樣，如果索引是組合索引，如(1)所示，而且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’語句返回多行數(shù)據(jù)，雖然該語句還是使用該組合索引進行查詢，可此時的存取方法稱為索引范圍掃描。 Query Plan SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX RANGE SCAN EMP_I1 [ANALYZED] 從這個例子中可以看到：因為索引是已經(jīng)排序了的，所以將按照索引的順序查詢出符合條件的行，因此避免了進一步排序操作。 但是如果查詢的數(shù)據(jù)能全在索引中找到，就可以避免進行第2步操作，避免了不必要的I/O，此時即使通過索引掃描取出的數(shù)據(jù)比較多，效率還是很高的，因為這只會在索引中讀取。索引掃描可以由2步組成：(1) 掃描索引得到對應(yīng)的rowid值。Oracle然后以得到的ROWID為依據(jù)定位每個被選擇的行。在這種訪問模式下，每個數(shù)據(jù)塊只被讀一次?，F(xiàn)在先來看一些預(yù)備知識。 對每個由子步驟依次檢索出來的每一行，Oracle就實現(xiàn)父步驟及所有串聯(lián)在一起的步驟一次。 Oracle實現(xiàn)步驟６，如果有結(jié)果行的話，將它返回給第１步。它接收來自第２步和第６步的行源，消除掉第２步中來的，在第６步有相應(yīng)行的那些行，并將來自第２步的剩下的行返回給發(fā)出語句的用戶或應(yīng)用。一般來說這并不是每一步被執(zhí)行的先后順序。下面先從分析樹型執(zhí)行計劃開始介紹，然后介紹如何得到執(zhí)行計劃，再介紹如何分析執(zhí)行計劃。在可選擇性高的列上進行查詢時，返回的數(shù)據(jù)就較少，比較適合使用索引查詢。所以該表應(yīng)當(dāng)為大表(實際上應(yīng)該為返回較大row source的表)且相應(yīng)的列上應(yīng)該有索引。這個概念用于嵌套與HASH連接中。recursive SQL statements39。我們創(chuàng)建一個索引時，該索引不但存儲索引列的值，而且也存儲索引值所對應(yīng)的行的ROWID，這樣我們通過索引快速找到相應(yīng)行的ROWID后，通過該ROWID，就可以迅速將數(shù)據(jù)查詢出來。因為有可能該變該值后，你的硬編碼SQL是可以使用共享SQL了，但數(shù)據(jù)庫的性能反而會下降。 int_empno = 2000。 與 用戶user2發(fā)出的語句：SELECT * FROM EMP。 SELECT * FROM emp WHERE empno = 2000。使用這個功能的關(guān)鍵是將執(zhí)行過的語句盡可能放到內(nèi)存中，所以這要求有大的共享池(通過設(shè)置shared buffer pool參數(shù)值)和盡可能的使用綁定變量的方法執(zhí)行SQL語句。這是需要人工干預(yù)數(shù)據(jù)庫優(yōu)化的主要原因。 注意：即使指定數(shù)據(jù)庫使用RBO優(yōu)化器，但有時ORACLE數(shù)據(jù)庫還是會采用CBO優(yōu)化器，這并不是ORACLE的BUG，主要是由于從ORACLE 8I后引入的許多新特性都必須在CBO下才能使用，而你的SQL語句可能正好使用了這些新特性，此時數(shù)據(jù)庫會自動轉(zhuǎn)為使用CBO優(yōu)化器執(zhí)行這些語句。具體解釋如下： RULE為使用RBO優(yōu)化器。2）CBO不是很穩(wěn)定，但是一個比較有前途的優(yōu)化器，Oracle極力建議大家用是為了讓大家盡快發(fā)現(xiàn)它的BUG，以便進一步改善，但是ORACLE為了對自己開發(fā)的應(yīng)用系統(tǒng)負責(zé)，他們還是使用了比較熟悉而且成熟的RBO。 對于分析用的命令，隨著數(shù)據(jù)庫版本的升級，用的命令也發(fā)生了變換，在oracle 8i以前，主要是用ANALYZE命令。CPU代價是處理在內(nèi)存中數(shù)據(jù)所需要的代價，如一旦數(shù)據(jù)被讀入內(nèi)存，則我們在識別出我們需要的數(shù)據(jù)后，在這些數(shù)據(jù)上執(zhí)行排序(sort)或連接(join)操作，這需要耗費CPU資源。 下面就是基于規(guī)則的優(yōu)化器使用的執(zhí)行路徑與各個路徑對應(yīng)的等級： RBO Path 1: Single Row by Rowid(等級最高) RBO Path 2: Single Row by Cluster Join RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key RBO Path 4: Single Row by Unique or Primary Key RBO Path 5: Clustered Join RBO Path 6: Hash Cluster Key RBO Path 7: Indexed Cluster Key RBO Path 8: Composite Index RBO Path 9: SingleColumn Indexes RBO Path 10: Bounded Range Search on Indexed Columns RBO Path 11: Unbounded Range Search on Indexed Columns RBO Path 12: Sort Merge Join RBO Path 13: MAX or MIN of Indexed Column RBO Path 14: ORDER BY on Indexed Column RBO Path 15: Full Table Scan(等級最低) 上面的執(zhí)行路徑中，RBO認(rèn)為越往下執(zhí)行的代價越大，即等級越低。 上面是枯燥的假設(shè)數(shù)據(jù)，現(xiàn)在以具體的實例給予驗證： 環(huán)境: oracle 817 + linux + 陣列柜，表SWD_BILLDETAIL有3200多萬數(shù)據(jù)； 表的id列、列上都有索引 經(jīng)查看執(zhí)行計劃，發(fā)現(xiàn)執(zhí)行select count(id) from SWD_BILLDETAIL。 2) emp表比較大時，而且deptno = 10條件能查詢出表中大部分的數(shù)據(jù)如(50%)。此時數(shù)據(jù)庫中沒有關(guān)于表與索引數(shù)據(jù)的統(tǒng)計性描述，如表中有多上行，每行的可選擇性等。為了在多個執(zhí)行計劃中選擇最優(yōu)的執(zhí)行計劃，優(yōu)化器必須使用一些實際的指標(biāo)來衡量每個執(zhí)行計劃使用的資源(I/0次數(shù)、CPU等)，這些資源也就是我們所說的代價(cost)。 第2章 ORACLE的優(yōu)化器優(yōu)化器有時也被稱為查詢優(yōu)化器，這是因為查詢是影響數(shù)據(jù)庫性能最主要的部分，不要以為只有SELECT語句是查詢。 事務(wù)管理語句、會話管理語句、系統(tǒng)管理語句只有分析與執(zhí)行階段，為了重新執(zhí)行該語句，會重新分析與執(zhí)行該語句。然而，如果語句為UPDATE或DELETE語句，則該語句影響的所有行都被鎖定，防止該用戶提交或回滾之前，別的用戶對這些數(shù)據(jù)進行修改。 因為你指定了變量名，在你再次執(zhí)行之前無須重新捆綁變量。如果必要的話，Oracle會自動實現(xiàn)數(shù)據(jù)類型的轉(zhuǎn)換。 查詢語句的處理 查詢與其它類型的SQL語句不同，因為在成功執(zhí)行后作為結(jié)果將返回數(shù)據(jù)。在這種情況下，數(shù)據(jù)庫內(nèi)核重新為該語句分配新的共享SQL區(qū)，并對語句進行語法分析。任何SQL語句都會創(chuàng)建它，特別在運行DML語句時，都是自動創(chuàng)建游標(biāo)的，不需要開發(fā)人員干預(yù)。 對許多oracle的工具來說，其中某些階段會自動執(zhí)行。如DEFINE階段可能在FETCH階段之前，這主要依賴你如何書寫代碼。 每種類型的語句都需要如下階段： ? 第1步: Create a Cursor 創(chuàng)建游標(biāo) ? 第2步: Parse the Statement 分析語句 ? 第5步: Bind Any Variables 綁定變量 ? 第7步: Run the Statement 運行語句 ? 第9步: Close the Cursor 關(guān)閉游標(biāo) 如果使用了并行功能，還會包含下面這個階段： ? 第6步: Parallelize the Statement 并行執(zhí)行語句 如果是查詢語句，則需要以下幾個額外的步驟，如圖 3所示： ? 第3步: Describe Results of a Query 描述查詢的結(jié)果集 ? 第4步: Define Output of a Query 定義查詢的輸出數(shù)據(jù) ? 第8步: Fetch Rows of a Query 取查詢出來的行 下面具體說一下每一步中都發(fā)生了什么事情：. 第1步: 創(chuàng)建游標(biāo)(Create a Cursor) 由程序接口調(diào)用創(chuàng)建一個游標(biāo)（cursor）。 只有在共享池中不存在等價SQL語句的情況下，才對SQL語句作語法分析。例如，在數(shù)據(jù)轉(zhuǎn)換中的錯誤或在數(shù)據(jù)中的錯（如企圖在主鍵中插入重復(fù)的值）以及死鎖等均是只有在語句執(zhí)行階段期間才能遇到和報告的錯誤或情況。 第4步: 定義查詢的輸出數(shù)據(jù)(Define Output of a Query) 在查詢的定義階段，你指定與查詢出的列值對應(yīng)的接收變量的位置、大小和數(shù)據(jù)類型，這樣我們通過接收變量就可以得到查詢結(jié)果。應(yīng)用的最終用戶可能并沒有發(fā)覺他們正在指定捆綁變量，因為Oracle 的程序可能只是簡單地指示他們輸入新的值，其實這一切都在程序中自動做了。如果該語句為SELECT查詢或INSERT語句，則不需要鎖定任何行，因為沒有數(shù)據(jù)需要被改變。對于DDL語句，語句的分析階段實際上包括分析、查找數(shù)據(jù)字典信息和執(zhí)行。 在設(shè)計應(yīng)用時，除了需要決定哪種類型的操作組成一個事務(wù)外，還需要決定使用BEGIN_DISCRETE_TRANSACTIO存儲過程是否對提高小的、非分布式的事務(wù)的性能有作用。例如，參與連接的表可以有多種不同的連接方法，這取決于連接條件和優(yōu)化器采用的連接方法。例如，如果一個查詢的where條件(where clause)包含一個謂詞(predicate，其實就是一個判斷條件，如”=”, “”, ””等)，而且該謂詞上引用的列上有有效索引，那么優(yōu)化器將使用索引訪問這個表，而不考慮其它因素，如表中數(shù)據(jù)的多少、表中數(shù)據(jù)的易變性、索引的可選擇性等。而如果使用索引掃描，需要先從索引中找到符合條件記錄的rowid，然后再一一根據(jù)這些rowid從emp中將數(shù)據(jù)取出來，在這種條件下，效率就會比全表掃描的效率要差一些。在這樣的情況下，用全表掃描的時間是固定的，但是用索引掃描的時間會隨著選出數(shù)據(jù)的增多使查詢時間相應(yīng)的延長。呢？后面在分析執(zhí)行路徑的索引掃描時時會給出說明。訪問數(shù)據(jù)包括將數(shù)據(jù)文件中數(shù)據(jù)塊的內(nèi)容讀入到SGA的數(shù)據(jù)高速緩存中，在一般情況下，該代價是處理一個查詢所需要的最主要代價，所以我們在優(yōu)化時，一個基本原則就是降低查詢所產(chǎn)生的I/O總次