.jpg)
本篇文章給大家?guī)淼膬?nèi)容是關(guān)于oracle表連接方式的優(yōu)化方法（附示例），有一定的參考價(jià)值，有需要的朋友可以參考一下，希望對(duì)你有所幫助。
在oracle數(shù)據(jù)庫(kù)中，兩個(gè)表之間的表連接方法有排序合并連接、嵌套循環(huán)連接、哈希連接和笛卡爾連接四種
1.排序合并連接（sort merge join）
排序合并連接是一種兩表在做表連接時(shí)用排序（SORT）操作和合并（MERGE）操作來得到連接結(jié)果集的表連接方法
如果t1表和t2表在做表連接時(shí)使用的是排序合并連接，那么Oracle會(huì)依次執(zhí)行如下步驟：
a.以目標(biāo)sql中指定的謂詞條件訪問t1表，然后對(duì)訪問結(jié)果按照t1表的連接列排序，排好序后的結(jié)果集記為s1
b.以目標(biāo)SQL中指定的謂詞條件訪問t2表，然后對(duì)訪問結(jié)果按照t2表的連接列排序，排好序后的結(jié)果集記為s2
c.對(duì)s1和s2進(jìn)行合并操作，從中取出匹配記錄作為最終的結(jié)果集
排序合并連接的優(yōu)缺點(diǎn)及適用場(chǎng)景：
a.通常情況下hash join的效果都比sort merge join要好，但是，如果行源已經(jīng)被排過序，在執(zhí)行sort merge join時(shí)不需要再排序，這時(shí)sort merge join的性能會(huì)優(yōu)于hash join
b.通常情況下，只有在以下情況發(fā)生時(shí)，才會(huì)使用排序合并連接：
1)RBO模式
2)不等值連接（>,=,
3)哈希連接被禁用時(shí)（_HASH_JOIN_ENABLED=false）
示例
SQL>?select?*?from?scott.emp?t1,scott.emp?t2?where?t1.empno?>?t2.mgr; 89?rows?selected. Execution?Plan ---------------------------------------------------------- Plan?hash?value:?3950110903 ---------------------------------------------------------------------------------------- |?Id??|?Operation?????????????????????|?Name???|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????| ---------------------------------------------------------------------------------------- |???0?|?SELECT?STATEMENT??????????????|????????|????62?|??4712?|?????6??(17)|?00:00:01?| |???1?|??MERGE?JOIN???????????????????|????????|????62?|??4712?|?????6??(17)|?00:00:01?| |???2?|???SORT?JOIN???????????????????|????????|????14?|???532?|?????2???(0)|?00:00:01?| |???3?|????table?ACCESS?BY?INDEX?ROWID|?EMP????|????14?|???532?|?????2???(0)|?00:00:01?| |???4?|?????INDEX?FULL?SCAN???????????|?PK_EMP?|????14?|???????|?????1???(0)|?00:00:01?| |*??5?|???SORT?JOIN???????????????????|????????|????14?|???532?|?????4??(25)|?00:00:01?| |???6?|????TABLE?ACCESS?FULL??????????|?EMP????|????14?|???532?|?????3???(0)|?00:00:01?| ---------------------------------------------------------------------------------------- Predicate?Information?(identified?by?operation?id): --------------------------------------------------- ???5?-?access(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR")) ???????filter(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR")) Statistics ---------------------------------------------------------- ??????????1??recursive?calls ??????????0??db?block?gets ??????????8??consistent?gets ??????????0??physical?reads ??????????0??redo?size ???????6612??bytes?sent?via?SQL*Net?to?client ????????575??bytes?received?via?SQL*Net?from?client ??????????7??SQL*Net?roundtrips?to/from?client ??????????2??sorts?(memory) ??????????0??sorts?(disk) ?????????89??rows?processed SQL>
2.嵌套循環(huán)連接（nested loops join）
嵌套循環(huán)連接是一種兩表在做表連接時(shí)依靠?jī)蓪忧短籽h(huán)（外層循環(huán)/內(nèi)層循環(huán)）來得到連接結(jié)果集的表連接方法
如果t1表和t2表在做表連接時(shí)使用的是嵌套循環(huán)連接，那么Oracle會(huì)依次執(zhí)行如下步驟：
a.首先，優(yōu)化器會(huì)按照一定的規(guī)則來決定t1和t2誰是驅(qū)動(dòng)表誰是被驅(qū)動(dòng)表，驅(qū)動(dòng)表用于外層循環(huán)，被驅(qū)動(dòng)表用于內(nèi)存循環(huán)。假設(shè)t1是驅(qū)動(dòng)表
b.以目標(biāo)SQL中指定的謂詞條件訪問驅(qū)動(dòng)表t1，得到結(jié)果集s1
c.遍歷s1，同時(shí)遍歷被驅(qū)動(dòng)表t2，即取出s1中的記錄按照連接條件和被驅(qū)動(dòng)表t2做匹配。最終將得到的結(jié)果集返回
嵌套循環(huán)連接的優(yōu)缺點(diǎn)及適用場(chǎng)景：
a.能夠?qū)崿F(xiàn)快速響應(yīng)，即可以第一時(shí)間先返回已經(jīng)連接過且滿足連接條件的記錄，而不必等待所有的連接操作全部做完后才返回連接結(jié)果
b.適用于驅(qū)動(dòng)表所對(duì)應(yīng)的驅(qū)動(dòng)結(jié)果集的記錄數(shù)較少，同時(shí)在被驅(qū)動(dòng)表的連接列上又存在唯一性索引（或者在被驅(qū)動(dòng)表的連接列上存在選擇性很好的非唯一性索引）的情況
示例
SQL>?select?/*+?gather_plan_statistics?use_nl(t1,t2)*/*?from?scott.emp?t1,scott.dept?t2?where?t1.deptno?=?t2.deptno; SQL>?select?*?from?table(dbms_xplan.display_cursor(null,0,'allstats,last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID??dcsf9m1rzzga5,?child?number?0 ------------------------------------- select?/*+?gather_plan_statistics?use_nl(t1,t2)*/*?from?scott.emp t1,scott.dept?t2?where?t1.deptno?=?t2.deptno Plan?hash?value:?4192419542 ------------------------------------------------------------------------------------- |?Id??|?Operation??????????|?Name?|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?| ------------------------------------------------------------------------------------- |???0?|?SELECT?STATEMENT???|??????|??????1?|????????|?????14?|00:00:00.01?|??????32?| |???1?|??NESTED?LOOPS??????|??????|??????1?|?????14?|?????14?|00:00:00.01?|??????32?| |???2?|???TABLE?ACCESS?FULL|?DEPT?|??????1?|??????4?|??????4?|00:00:00.01?|???????7?| |*??3?|???TABLE?ACCESS?FULL|?EMP??|??????4?|??????4?|?????14?|00:00:00.01?|??????25?| ------------------------------------------------------------------------------------- Predicate?Information?(identified?by?operation?id): --------------------------------------------------- -?filter("T1"."DEPTNO"="T2"."DEPTNO") rows?selected. SQL>
3.哈希連接（hash join）
哈希連接是一種兩表在做表連接時(shí)依靠哈希運(yùn)算來得到連接結(jié)果集的表連接方法，oracle 7.3之后引入
Hash join的工作方式是將一個(gè)表（通常是小一點(diǎn)的那個(gè)表）做hash運(yùn)算并存儲(chǔ)到hash列表中，從另一個(gè)表中抽取記錄，做hash運(yùn)算，到hash 列表中找到相應(yīng)的值，做匹配
哈希連接只適用于CBO，也只能用于等值連接條件
哈希連接很適合于小表和大表做連接，特別是在小表的連接列的可選擇性非常好的情況下，這時(shí)候哈希連接的執(zhí)行時(shí)間就可以近似看作是和全表掃描那個(gè)大表所耗費(fèi)的時(shí)間相當(dāng)
哈希連接時(shí)，驅(qū)動(dòng)結(jié)果集對(duì)應(yīng)的Hash Table能夠完全被容納在內(nèi)存中（PGA的工作區(qū)），此時(shí)的哈希連接的執(zhí)行效率非常高
哈希連接的性能問題可以通過10104事件來診斷，相關(guān)說明如下：
Number of in-memory partitions (may have changed): Hash Partition
Final number of hash buckets: Hash Bucket數(shù)量
Total buckets: ?Empty buckets: ?Non-empty buckets: Hash Bucket中空記錄及非空記錄的情況
Total number of rows: 驅(qū)動(dòng)結(jié)果集的記錄數(shù)
Maximum number of rows in a bucket: 包含記錄數(shù)最多的Hash Bucket所含記錄的數(shù)量
Disabled bitmap filtering: 是否啟用位圖過濾
示例
SQL>?select?/*+?gather_plan_statistics?use_hash(t1,t2)*/*?from?scott.emp?t1,scott.dept?t2?where?t1.deptno?=?t2.deptno; SQL>?select?*?from?table(dbms_xplan.display_cursor(null,0,'allstats,last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID??0j83q86ara5u2,?child?number?0 ------------------------------------- select?/*+?gather_plan_statistics?use_hash(t1,t2)*/*?from?scott.emp t1,scott.dept?t2?where?t1.deptno?=?t2.deptno Plan?hash?value:?615168685 ---------------------------------------------------------------------------------------------------------------- |?Id??|?Operation??????????|?Name?|?Starts?|?E-Rows?|?A-Rows?|???A-Time???|?Buffers?|??OMem?|??1Mem?|?Used-Mem?| ---------------------------------------------------------------------------------------------------------------- |???0?|?SELECT?STATEMENT???|??????|??????1?|????????|?????14?|00:00:00.01?|??????13?|???????|???????|??????????| |*??1?|??HASH?JOIN?????????|??????|??????1?|?????14?|?????14?|00:00:00.01?|??????13?|??1321K|??1321K|?1070K?(0)| |???2?|???TABLE?ACCESS?FULL|?DEPT?|??????1?|??????4?|??????4?|00:00:00.01?|???????6?|???????|???????|??????????| |???3?|???TABLE?ACCESS?FULL|?EMP??|??????1?|?????14?|?????14?|00:00:00.01?|???????7?|???????|???????|??????????| ---------------------------------------------------------------------------------------------------------------- Predicate?Information?(identified?by?operation?id): --------------------------------------------------- ???1?-?access("T1"."DEPTNO"="T2"."DEPTNO") 21?rows?selected. SQL>
【相關(guān)推薦：SQL教程】
.png)
