轉(zhuǎn)自：http://blog.chinaunix.net/u3/107265/showart_2192657.html
簡介：
本文全面詳細(xì)介紹oracle執(zhí)行計(jì)劃的相關(guān)的概念，訪問數(shù)據(jù)的存取方法，表之間的連接等內(nèi)容。
并有總結(jié)和概述，便于理解與記憶!
+++
目錄
---
一．相關(guān)的概念
Rowid的概念
Recursive Sql概念
Predicate(謂詞)
DRiving Table(驅(qū)動(dòng)表)
Probed Table(被探查表)
組合索引(concatenated index)
可選擇性(selectivity)
二．oracle訪問數(shù)據(jù)的存取方法
1） 全表掃描（Full Table Scans， FTS）
2） 通過ROWID的表存取（Table Access by ROWID或rowid lookup）
3）索引掃描（Index Scan或index lookup）有4種類型的索引掃描：
（1） 索引唯一掃描（index unique scan）
（2） 索引范圍掃描（index range scan）
在非唯一索引上都使用索引范圍掃描。使用index rang scan的3種情況：
（a） 在唯一索引列上使用了range操作符（> < <> >= <= between）
（b） 在組合索引上，只使用部分列進(jìn)行查詢，導(dǎo)致查詢出多行
（c） 對非唯一索引列上進(jìn)行的任何查詢。
（3） 索引全掃描（index full scan）
（4） 索引快速掃描（index fast full scan）
三、表之間的連接
1，排序 - - 合并連接（Sort Merge Join， SMJ）
2，嵌套循環(huán)（Nested Loops， NL）
3，哈希連接（Hash Join， HJ）
另外，笛卡兒乘積（Cartesian Product）
總結(jié)Oracle連接方法
Oracle執(zhí)行計(jì)劃總結(jié)概述
+++
一．相關(guān)的概念
Rowid的概念：rowid是一個(gè)偽列，既然是偽列，那么這個(gè)列就不是用戶定義，而是系統(tǒng)自己給加上的。 對每個(gè)表都有一個(gè)rowid的偽列，但是表中并不物理存儲ROWID列的值。不過你可以像使用其它列那樣使用它，但是不能刪除改列，也不能對該列的值進(jìn)行 修改、插入。一旦一行數(shù)據(jù)插入數(shù)據(jù)庫，則rowid在該行的生命周期內(nèi)是唯一的，即即使該行產(chǎn)生行遷移，行的rowid也不會改變。
Recursive SQL概念：有時(shí)為了執(zhí)行用戶發(fā)出的一個(gè)sql語句，Oracle必須執(zhí)行一些額外的語句，我們將這些額外的語句稱之為''recursive calls''或''recursive SQL statements''.如當(dāng)一個(gè)DDL語句發(fā)出后，ORACLE總是隱含的發(fā)出一些recursive SQL語句，來修改數(shù)據(jù)字典信息，以便用戶可以成功的執(zhí)行該DDL語句。當(dāng)需要的數(shù)據(jù)字典信息沒有在共享內(nèi)存中時(shí)，經(jīng)常會發(fā)生Recursive calls，這些Recursive calls會將數(shù)據(jù)字典信息從硬盤讀入內(nèi)存中。用戶不比關(guān)心這些recursive SQL語句的執(zhí)行情況，在需要的時(shí)候，ORACLE會自動(dòng)的在內(nèi)部執(zhí)行這些語句。當(dāng)然DML語句與SELECT都可能引起recursive SQL.簡單的說，我們可以將觸發(fā)器視為recursive SQL.
Row Source（行源）：用在查詢中，由上一操作返回的符合條件的行的集合，即可以是表的全部行數(shù)據(jù)的集合；也可以是表的部分行數(shù)據(jù)的集合；也可以為對上2個(gè)row source進(jìn)行連接操作（如join連接）后得到的行數(shù)據(jù)集合。
Predicate（謂詞）：一個(gè)查詢中的WHERE限制條件
Driving Table（驅(qū)動(dòng)表）：該表又稱為外層表（OUTER TABLE）。這個(gè)概念用于嵌套與HASH連接中。如果該row source返回較多的行數(shù)據(jù)，則對所有的后續(xù)操作有負(fù)面影響。注意此處雖然翻譯為驅(qū)動(dòng)表，但實(shí)際上翻譯為驅(qū)動(dòng)行源（driving row source）更為確切。一般說來，是應(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è)row source，后面會給出具體說明。在我們后面的描述中，一般將該表稱為連接操作的row source 1.
Probed Table（被探查表）：該表又稱為內(nèi)層表（INNER TABLE）。在我們從驅(qū)動(dòng)表中得到具體一行的數(shù)據(jù)后，在該表中尋找符合連接條件的行。所以該表應(yīng)當(dāng)為大表（實(shí)際上應(yīng)該為返回較大row source的表）且相應(yīng)的列上應(yīng)該有索引。在我們后面的描述中，一般將該表稱為連接操作的row source 2.
組合索引（concatenated index）：由多個(gè)列構(gòu)成的索引，如create index idx_emp on emp（col1， col2， col3， ……），則我們稱idx_emp索引為組合索引。在組合索引中有一個(gè)重要的概念：引導(dǎo)列（leading column），在上面的例子中，col1列為引導(dǎo)列。當(dāng)我們進(jìn)行查詢時(shí)可以使用“where col1 = ？ ”，也可以使用“where col1 = ？ and col2 = ？”，這樣的限制條件都會使用索引，但是“where col2 = ？ ”查詢就不會使用該索引。所以限制條件中包含先導(dǎo)列時(shí)，該限制條件才會使用該組合索引。
可選擇性（selectivity）：比較一下列中唯一鍵的數(shù)量和表中的行數(shù)，就可以判斷該列的可選擇性。 如果該列的“唯一鍵的數(shù)量/表中的行數(shù)”的比值越接近1，則該列的可選擇性越高，該列就越適合創(chuàng)建索引，同樣索引的可選擇性也越高。在可選擇性高的列上進(jìn) 行查詢時(shí)，返回的數(shù)據(jù)就較少，比較適合使用索引查詢。
二．oracle訪問數(shù)據(jù)的存取方法
1） 全表掃描（Full Table Scans， FTS）
為實(shí)現(xiàn)全表掃描，Oracle讀取表中所有的行，并檢查每一行是否滿足語句的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> explain plan for select * from dual;
Query Plan
-----------------------------------------
SELECT STATEMENT[CHOOSE] Cost=
TABLE ACCESS FULL DUAL
2） 通過ROWID的表存?。═able Access by ROWID或rowid lookup）
行的ROWID指出了該行所在的數(shù)據(jù)文件、數(shù)據(jù)塊以及行在該塊中的位置，所以通過ROWID來存取數(shù)據(jù)可以快速定位到目標(biāo)數(shù)據(jù)上，是Oracle存取單行數(shù)據(jù)的最快方法。
這種存取方法不會用到多塊讀操作，一次I/O只能讀取一個(gè)數(shù)據(jù)塊。我們會經(jīng)常在執(zhí)行計(jì)劃中看到該存取方法，如通過索引查詢數(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值（對于非唯一索引可能返回多個(gè)rowid值），然后根據(jù)rowid直接從表中得到具體的數(shù)據(jù)，這 種查找方式稱為索引掃描或索引查找（index lookup）。一個(gè)rowid唯一的表示一行數(shù)據(jù)，該行對應(yīng)的數(shù)據(jù)塊是通過一次i/o得到的，在此情況下該次i/o只會讀取一個(gè)數(shù)據(jù)庫塊。
在索引中，除了存儲每個(gè)索引的值外，索引還存儲具有此值的行對應(yīng)的ROWID值。
索引掃描可以由2步組成：
（1） 掃描索引得到對應(yīng)的rowid值。
（2） 通過找到的rowid從表中讀出具體的數(shù)據(jù)。
每步都是單獨(dú)的一次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，這 是一個(gè)機(jī)械操作，相對邏輯I/O來說，是極其費(fèi)時(shí)間的。所以如果多大表進(jìn)行索引掃描，取出的數(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ù)能全在索引中找到，就可以避免進(jìn)行第2步操作，避免了不必要的I/O，此時(shí)即使通過索引掃描取出的數(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
進(jìn)一步講，如果sql語句中對索引列進(jìn)行排序，因?yàn)樗饕呀?jīng)預(yù)先排序好了，所以在執(zhí)行計(jì)劃中不需要再對索引列進(jìn)行排序
SQL> explain plan for select empno, ename from emp
where empno > 7876 order by empno;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]
從這個(gè)例子中可以看到：因?yàn)樗饕且呀?jīng)排序了的，所以將按照索引的順序查詢出符合條件的行，因此避免了進(jìn)一步排序操作。
根據(jù)索引的類型與where限制條件的不同，有4種類型的索引掃描：
索引唯一掃描（index unique scan）
索引范圍掃描（index range scan）
索引全掃描（index full scan）
索引快速掃描（index fast full scan）
（1） 索引唯一掃描（index unique scan）
通過唯一索引查找一個(gè)數(shù)值經(jīng)常返回單個(gè)ROWID.如果存在UNIQUE 或PRIMARY KEY 約束（它保證了語句只存取單行）的話，Oracle經(jīng)常實(shí)現(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）
使用一個(gè)索引存取多行數(shù)據(jù)，在唯一索引上使用索引范圍掃描的典型情況下是在謂詞（where限制條件）中使用了范圍操作符（如>、、>=、<=、between）
使用索引范圍掃描的例子：
SQL> explain plan for select empno，ename from emp
where empno > 7876 order by empno；
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]
在非唯一索引上，謂詞col = 5可能返回多行數(shù)據(jù)，所以在非唯一索引上都使用索引范圍掃描。
使用index rang scan的3種情況：
（a） 在唯一索引列上使用了range操作符（> < <> >= <= between）
（b） 在組合索引上，只使用部分列進(jìn)行查詢，導(dǎo)致查詢出多行
（c） 對非唯一索引列上進(jìn)行的任何查詢。
（3） 索引全掃描（index full scan）
與全表掃描對應(yīng)，也有相應(yīng)的全索引掃描。而且此時(shí)查詢出的數(shù)據(jù)都必須從索引中可以直接得到。
全索引掃描的例子：
An Index full scan will not perform. single block i/o''s and so it may prove to be inefficient.
e.g.
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=26
INDEX FULL SCAN BE_IX [ANALYZED]
（4） 索引快速掃描（index fast full scan）
掃描索引中的所有的數(shù)據(jù)塊，與 index full scan很類似，但是一個(gè)顯著的區(qū)別就是它不對查詢出的數(shù)據(jù)進(jìn)行排序，即數(shù)據(jù)不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執(zhí)行時(shí)間。
索引快速掃描的例子：
BE_IX索引是一個(gè)多列索引： big_emp （empno，ename）
SQL> explain plan for select empno，ename from big_emp；
Query Plan
------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]
只選擇多列索引的第2列：
SQL> explain plan for select ename from big_emp；
Query Plan
------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]
三、表之間的連接
Join是一種試圖將兩個(gè)表結(jié)合在一起的謂詞，一次只能連接2個(gè)表，表連接也可以被稱為表關(guān)聯(lián)。在后面的敘 述中，我們將會使用“row source”來代替“表”，因?yàn)槭褂胷ow source更嚴(yán)謹(jǐn)一些，并且將參與連接的2個(gè)row source分別稱為row source1和row source 2.Join過程的各個(gè)步驟經(jīng)常是串行操作，即使相關(guān)的row source可以被并行訪問，即可以并行的讀取做join連接的兩個(gè)row source的數(shù)據(jù)，但是在將表中符合限制條件的數(shù)據(jù)讀入到內(nèi)存形成row source后，join的其它步驟一般是串行的。有多種方法可以將2個(gè)表連接起來，當(dāng)然每種方法都有自己的優(yōu)缺點(diǎn)，每種連接類型只有在特定的條件下才會 發(fā)揮出其最大優(yōu)勢。
row source（表）之間的連接順序?qū)τ诓樵兊男视蟹浅４蟮挠绊憽Ｍㄟ^首先存取特定的表，即將該表作為驅(qū)動(dòng)表，這樣可以先應(yīng)用某些限制條件，從而得到一個(gè) 較小的row source，使連接的效率較高，這也就是我們常說的要先執(zhí)行限制條件的原因。一般是在將表讀入內(nèi)存時(shí)，應(yīng)用where子句中對該表的限制條件。
根據(jù)2個(gè)row source的連接條件的中操作符的不同，可以將連接分為等值連接（如WHERE A.COL3 = B.COL4）、非等值連接（WHERE A.COL3 > B.COL4）、外連接（WHERE A.COL3 = B.COL4（+））。上面的各個(gè)連接的連接原理都基本一樣，所以為了簡單期間，下面以等值連接為例進(jìn)行介紹。
在后面的介紹中，都以以下Sql為例進(jìn)行說明：
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）
另外，還有一種Cartesian product（笛卡爾積），一般情況下，盡量避免使用。
1，排序 - - 合并連接（Sort Merge Join， SMJ）
內(nèi)部連接過程：
1） 首先生成row source1需要的數(shù)據(jù)，然后對這些數(shù)據(jù)按照連接操作關(guān)聯(lián)列（如A.col3）進(jìn)行排序。
2） 隨后生成row source2需要的數(shù)據(jù)，然后對這些數(shù)據(jù)按照與sort source1對應(yīng)的連接操作關(guān)聯(lián)列（如B.col4）進(jìn)行排序。
3） 最后兩邊已排序的行被放在一起執(zhí)行合并操作，即將2個(gè)row source按照連接條件連接起來
下面是連接步驟的圖形表示：
MERGE
/\
SORTSORT
||
Row Source 1Row Source 2
如果row source已經(jīng)在連接關(guān)聯(lián)列上被排序，則該連接操作就不需要再進(jìn)行sort操作，這樣可以大大提高這種連接操作的連接速度，因?yàn)榕判蚴莻€(gè)極其費(fèi)資源的操 作，特別是對于較大的表。預(yù)先排序的row source包括已經(jīng)被索引的列（如a.col3或b.col4上有索引）或row source已經(jīng)在前面的步驟中被排序了。盡管合并兩個(gè)row source的過程是串行的，但是可以并行訪問這兩個(gè)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]
排序是一個(gè)費(fèi)時(shí)、費(fèi)資源的操作，特別對于大表?；谶@個(gè)原因，SMJ經(jīng)常不是一個(gè)特別有效的連接方法，但是如果2個(gè)row source都已經(jīng)預(yù)先排序，則這種連接方法的效率也是蠻高的。
2，嵌套循環(huán)（Nested Loops， NL）
這個(gè)連接方法有驅(qū)動(dòng)表（外部表）的概念。其實(shí)，該連接過程就是一個(gè)2層嵌套循環(huán)，所以外層循環(huán)的次數(shù)越少越好，這也就是我們?yōu)槭裁磳⑿”砘蚍祷剌^小 row source的表作為驅(qū)動(dòng)表（用于外層循環(huán)）的理論依據(jù)。但是這個(gè)理論只是一般指導(dǎo)原則，因?yàn)樽裱@個(gè)理論并不能總保證使語句產(chǎn)生的I/O次數(shù)最少。有時(shí) 不遵守這個(gè)理論依據(jù)，反而會獲得更好的效率。如果使用這種方法，決定使用哪個(gè)表作為驅(qū)動(dòng)表很重要。有時(shí)如果驅(qū)動(dòng)表選擇不正確，將會導(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中的所有行，所以此時(shí)保持row source1盡可能的小與高效的訪問row source2（一般通過索引實(shí)現(xiàn)）是影響這個(gè)連接效率的關(guān)鍵問題。這只是理論指導(dǎo)原則，目的是使整個(gè)連接操作產(chǎn)生最少的物理I/O次數(shù)，而且如果遵守這 個(gè)原則，一般也會使總的物理I/O數(shù)最少。但是如果不遵從這個(gè)指導(dǎo)原則，反而能用更少的物理I/O實(shí)現(xiàn)連接操作，那盡管違反指導(dǎo)原則吧！因?yàn)樽钌俚奈锢?I/O次數(shù)才是我們應(yīng)該遵從的真正的指導(dǎo)原則，在后面的具體案例分析中就給出這樣的例子。
在上面的連接過程中，我們稱Row source1為驅(qū)動(dòng)表或外部表。Row Source2被稱為被探查表或內(nèi)部表。
在NESTED LOOPS連接中，Oracle讀取row source1中的每一行，然后在row sourc2中檢查是否有匹配的行，所有被匹配的行都被放到結(jié)果集中，然后處理row source1中的下一行。這個(gè)過程一直繼續(xù)，直到row source1中的所有行都被處理。這是從連接操作中可以得到第一個(gè)匹配行的最快的方法之一，這種類型的連接可以用在需要快速響應(yīng)的語句中，以響應(yīng)速度為 主要目標(biāo)。
如果driving row source（外部表）比較小，并且在inner row source（內(nèi)部表）上有唯一索引，或有高選擇性非唯一索引時(shí)，使用這種方法可以得到較好的效率。NESTED LOOPS有其它連接方法沒有的的一個(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)鍵看限制條件。對于并行查詢，我們經(jīng)常選擇大表作為驅(qū)動(dòng)表，因?yàn)榇蟊砜梢猿浞掷貌?行功能。當(dāng)然，有時(shí)對查詢使用并行操作并不一定會比查詢不使用并行操作效率高，因?yàn)樽詈罂赡苊總€(gè)表只有很少的行符合限制條件，而且還要看你的硬件配置是否 可以支持并行（如是否有多個(gè)CPU，多個(gè)硬盤控制器），所以要具體問題具體對待。
NL連接的例子：
SQL> explain plan for
select a.dname，b.sql
from dept a，emp b
where a.deptno = b.deptno；
Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost=5
NESTED LOOPS
TABLE ACCESS FULL DEPT [ANALYZED]
TABLE ACCESS FULL EMP [ANALYZED]
3，哈希連接（Hash Join， HJ）
這種連接是在oracle 7.3以后引入的，從理論上來說比NL與SMJ更高效，而且只用在CBO優(yōu)化器中。
較小的row source被用來構(gòu)建hash table與bitmap，第2個(gè)row source被用來被hansed，并與第一個(gè)row source生成的hash table進(jìn)行匹配，以便進(jìn)行進(jìn)一步的連接。Bitmap被用來作為一種比較快的查找方法，來檢查在hash table中是否有匹配的行。特別的，當(dāng)hash table比較大而不能全部容納在內(nèi)存中時(shí)，這種查找方法更為有用。這種連接方法也有NL連接中所謂的驅(qū)動(dòng)表的概念，被構(gòu)建為hash table與bitmap的表為驅(qū)動(dòng)表，當(dāng)被構(gòu)建的hash table與bitmap能被容納在內(nèi)存中時(shí)，這種連接方式的效率極高。
HASH連接的例子：
SQL> explain plan for
select /*+ use_hash（emp） */ empno
from emp， dept
where emp.deptno = dept.deptno；
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ù)，以使哈希連接高效運(yùn)行，因?yàn)楣＿B接會在該參數(shù)指定大小的內(nèi)存中運(yùn)行，過小的參數(shù)會使哈希連接的性能比其他連接方式還 要低。
另外，笛卡兒乘積（Cartesian Product）
當(dāng)兩個(gè)row source做連接，但是它們之間沒有關(guān)聯(lián)條件時(shí)，就會在兩個(gè)row source中做笛卡兒乘積，這通常由編寫代碼疏漏造成（即程序員忘了寫關(guān)聯(lián)條件）。笛卡爾乘積是一個(gè)表的每一行依次與另一個(gè)表中的所有行匹配。在特殊情況下我們可以使用笛卡兒乘積，如在星形連接中，除此之外，我們要盡量不使用笛卡兒乘積，否則，自己想結(jié)果是什么吧！
注意在下面的語句中，在2個(gè)表之間沒有連接。
SQL> explain plan for
select emp.deptno，dept，deptno
from emp，dept
Query Plan
------------------------
SLECT STATEMENT [CHOOSE] Cost=5
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DEPT
SORT JOIN
TABLE ACCESS FULL EMP
CARTESIAN關(guān)鍵字指出了在2個(gè)表之間做笛卡爾乘積。假如表emp有n行，dept表有m行，笛卡爾乘積的結(jié)果就是得到n * m行結(jié)果。
最后，總結(jié)一下，在哪種情況下用哪種連接方法比較好：
排序 - - 合并連接（Sort Merge Join， SMJ）：
a） 對于非等值連接，這種連接方式的效率是比較高的。
b） 如果在關(guān)聯(lián)的列上都有索引，效果更好。
c） 對于將2個(gè)較大的row source做連接，該連接方法比NL連接要好一些。
d） 但是如果sort merge返回的row source過大，則又會導(dǎo)致使用過多的rowid在表中查詢數(shù)據(jù)時(shí)，數(shù)據(jù)庫性能下降，因?yàn)檫^多的I/O.
嵌套循環(huán)（Nested Loops， NL）：
a） 如果driving row source（外部表）比較小，并且在inner row source（內(nèi)部表）上有唯一索引，或有高選擇性非唯一索引時(shí)，使用這種方法可以得到較好的效率。
b） NESTED LOOPS有其它連接方法沒有的的一個(gè)優(yōu)點(diǎn)是：可以先返回已經(jīng)連接的行，而不必等待所有的連接操作處理完才返回?cái)?shù)據(jù)，這可以實(shí)現(xiàn)快速的響應(yīng)時(shí)間。
哈希連接（Hash Join， HJ）：
a） 這種方法是在oracle7后來引入的，使用了比較先進(jìn)的連接理論，一般來說，其效率應(yīng)該好于其它2種連接，但是這種連接只能用在CBO優(yōu)化器中，而且需要設(shè)置合適的hash_area_size參數(shù)，才能取得較好的性能。
b） 在2個(gè)較大的row source之間連接時(shí)會取得相對較好的效率，在一個(gè)row source較小時(shí)則能取得更好的效率。
c） 只能用于等值連接中
+++
Oracle執(zhí)行計(jì)劃的概述
---
Oracle執(zhí)行計(jì)劃的相關(guān)概念：
Rowid：系統(tǒng)給oracle數(shù)據(jù)的每行附加的一個(gè)偽列，包含數(shù)據(jù)表名稱，數(shù)據(jù)庫id，存儲數(shù)據(jù)庫id以及一個(gè)流水號等信息，rowid在行的生命周期內(nèi)唯一。
Recursive sql：為了執(zhí)行用戶語句，系統(tǒng)附加執(zhí)行的額外操作語句，譬如對數(shù)據(jù)字典的維護(hù)等。
Row source（行源）：oracle執(zhí)行步驟過程中，由上一個(gè)操作返回的符合條件的行的集合。
Predicate（謂詞）：where后的限制條件。
Driving table（驅(qū)動(dòng)表）：又稱為連接的外層表，主要用于嵌套與hash連接中。一般來說是將應(yīng)用限制條件后，返回較少行源的表作為驅(qū)動(dòng)表。在后面的描述中，將driving table稱為連接操作的row source 1。
Probed table（被探查表）：連接的內(nèi)層表，在我們從driving table得到具體的一行數(shù)據(jù)后，在probed table中尋找符合條件的行，所以該表應(yīng)該為較大的row source，并且對應(yīng)連接條件的列上應(yīng)該有索引。在后面的描述中，一般將該表稱為連接操作的row source 2.
Concatenated index（組合索引）：一個(gè)索引如果由多列構(gòu)成，那么就稱為組合索引，組合索引的第一列為引導(dǎo)列，只有謂詞中包含引導(dǎo)列時(shí)，索引才可用。
可選擇性：表中某列的不同數(shù)值數(shù)量/表的總行數(shù)如果接近于1，則列的可選擇性為高。
Oracle訪問數(shù)據(jù)的存取方法：
Full table scans, FTS(全表掃描)：通過設(shè)置db_block_multiblock_read_count可以設(shè)置一次IO能讀取的數(shù)據(jù)塊個(gè)數(shù)，從而有效減少全表掃描時(shí)的IO總次數(shù)，也就是通過預(yù)讀機(jī)制將將要訪問的數(shù)據(jù)塊預(yù)先讀入內(nèi)存中。只有在全表掃描情況下才能使用多塊讀操作。
Table Access by rowed（通過rowid存取表，rowid lookup）：由于rowid中記錄了行存儲的位置，所以這是oracle存取單行數(shù)據(jù)的最快方法。
Index scan（索引掃描index lookup）：在索引中，除了存儲每個(gè)索引的值外，索引還存儲具有此值的行對應(yīng)的rowid值，索引掃描分兩步1，掃描索引得到rowid；2，通過 rowid讀取具體數(shù)據(jù)。每步都是單獨(dú)的一次IO，所以如果數(shù)據(jù)經(jīng)限制條件過濾后的總量大于原表總行數(shù)的5%－10％,則使用索引掃描效率下降很多。而如果結(jié)果數(shù)據(jù)能夠全部在索引中找到，則可以避免第二步操作，從而加快檢索速度。
根據(jù)索引類型與where限制條件的不同，有4種類型的索引掃描：
Index unique scan（索引唯一掃描）：存在unique或者primary key的情況下，返回單個(gè)rowid數(shù)據(jù)內(nèi)容。
Index range scan（索引范圍掃描）：1，在唯一索引上使用了range操作符（>,,>=,<=,between）；2，在組合索引上，只使用部分列進(jìn)行查詢；3，對非唯一索引上的列進(jìn)行的查詢。
Index full scan（索引全掃描）：需要查詢的數(shù)據(jù)從索引中可以全部得到。
Index fast full scan（索引快速掃描）：與index full scan類似，但是這種方式下不對結(jié)果進(jìn)行排序。
目前為止，典型的連接類型有3種：
Sort merge join（SMJ排序－合并連接）：首先生產(chǎn)driving table需要的數(shù)據(jù)，然后對這些數(shù)據(jù)按照連接操作關(guān)聯(lián)列進(jìn)行排序；然后生產(chǎn)probed table需要的數(shù)據(jù)，然后對這些數(shù)據(jù)按照與driving table對應(yīng)的連接操作列進(jìn)行排序；最后兩邊已經(jīng)排序的行被放在一起執(zhí)行合并操作。排序是一個(gè)費(fèi)時(shí)、費(fèi)資源的操作，特別對于大表。所以smj通常不是一個(gè)特別有效的連接方法，但是如果driving table和probed table都已經(jīng)預(yù)先排序，則這種連接方法的效率也比較高。
Nested loops（NL嵌套循環(huán)）：連接過程就是將driving table和probed table進(jìn)行一次嵌套循環(huán)的過程。就是用driving table的每一行去匹配probed table 的所有行。Nested loops可以先返回已經(jīng)連接的行，而不必等待所有的連接操作處理完成才返回?cái)?shù)據(jù)，這可以實(shí)現(xiàn)快速的響應(yīng)時(shí)間。
Hash join（哈希連接）：較小的row source被用來構(gòu)建hash table與bitmap，第二個(gè)row source用來被hashed，并與第一個(gè)row source生產(chǎn)的hash table進(jìn)行匹配。以便進(jìn)行進(jìn)一步的連接。當(dāng)被構(gòu)建的hash table與bitmap能被容納在內(nèi)存中時(shí)，這種連接方式的效率極高。但需要設(shè)置合適的hash_area_size參數(shù)且只能用于等值連接中。
另外，還有一種連接類型：Cartesian product（笛卡爾積）：表的每一行依次與另外一表的所有行匹配，一般情況下，盡量避免使用。
