共計(jì) 4330 個(gè)字符,預(yù)計(jì)需要花費(fèi) 11 分鐘才能閱讀完成。
這篇文章主要講解了“oracle 表連接的方法和類型有哪些”,文中的講解內(nèi)容簡(jiǎn)單清晰,易于學(xué)習(xí)與理解,下面請(qǐng)大家跟著丸趣 TV 小編的思路慢慢深入,一起來(lái)研究和學(xué)習(xí)“oracle 表連接的方法和類型有哪些”吧!
一、表連接就是在多個(gè)表之間用連接條件連接在一起,當(dāng)優(yōu)化器在解析帶有表連接的 sql 時(shí),除了會(huì)根據(jù) sql 的寫法來(lái)決定表連接的類型,還要確定下面三種情況才能指定出執(zhí)行計(jì)劃
1、表連接順序,不管幾個(gè)表的連接,sql 在執(zhí)行時(shí),只能進(jìn)行兩表的連接,然后根據(jù)連接結(jié)果再進(jìn)行下一個(gè)兩表的連接,直至所有表都連接完成,連接的順序有兩層含義,一是兩表連接時(shí)決定好誰(shuí)是 outer table (驅(qū)動(dòng)表)誰(shuí)是 inner table( 被驅(qū)動(dòng)表),二是在多表連接的情況下,先連接哪兩張表
2、表連接方法,有四種,排序合并連接,嵌套循環(huán)連接,哈希連接,笛卡爾連接,優(yōu)化器在解析 sql 時(shí)要決定采用哪種連接
3、訪問(wèn)單表的方法 優(yōu)化器在表表連接時(shí),還要決定如何去讀取單表中的數(shù)據(jù),比如用全表掃還是走索引,走索引的話該如何走索引,等等
二、表連接的類型
類型分為內(nèi)鏈接和外連接,類型決定了表連接的結(jié)果,sql 的寫法直接決定了類型
1、內(nèi)鏈接: 連接結(jié)果只包含那些完全滿足連接條件的記錄,只要 sql 里沒(méi)寫外連接的關(guān)鍵字,那就是內(nèi)鏈接, 內(nèi)鏈接的三種寫法,其中一是 oracle 專用的
SELECT T1.COL1,T1.COL2,T2.COL3 FROM T1,T2 WHERE T1.COL2=T2.COL2;
SELECT T1.COL1,T1.COL2,T2.COL3 FROM T1 JOIN T2 ON(T1.COL2=T2.COL2);
SELECT T1.COL1,
COL2,T2.COL3 FROM T1 JOIN T2 USING(COL2);
標(biāo)準(zhǔn) sql 中還有一種 natural join , 表示只用兩個(gè)表所有的同名列一起作為連接列,不推薦使用,雖然會(huì)省去寫連接列,但是會(huì)增加 sql 報(bào)錯(cuò)的風(fēng)險(xiǎn)
2、外連接,連接結(jié)果除了包含那些完全滿足連接條件的記錄,還包含所有驅(qū)動(dòng)表中不滿足連接條件的記錄,外連接分三種,左連接(left outer join), 右連接(right outer join),全連接(full outer join)
左連接,左邊為驅(qū)動(dòng)表
SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1,t2 where t1.col2=t2.col2(+);
SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1 left outer join T2 on (t1.col2=t2.col2);
SELECT T1.COL1, COL2,T2.COL3 FROM T1 left outer join T2 USING(COL2);
右連接,右邊為驅(qū)動(dòng)表
SELECT T1.COL1,t2.COL2,T2.COL3 FROM T1,t2 where t1.col2(+)=t2.col2;
SELECT T1.COL1, COL2,T2.COL3 FROM T1 right outer join T2 USING(COL2);
SELECT T1.COL1,t2.COL2,T2.COL3 from t1 right join t2 on(t1.col2=t2.col2);
全連接,相當(dāng)于左連接 union 右連接
SELECT T1.COL1,t1.COL2,T2.COL3 from t1 full join t2 on(t1.col2=t2.col2);
3、當(dāng)連接除了連接條件外,還有其他的條件時(shí)
內(nèi)鏈接下面?zhèn)z個(gè)語(yǔ)句結(jié)果相同
SELECT T1.COL1,t1.COL2,T2.COL3 from t1 join t2 on(t1.col2=t2.col2 and t1.col1=1);
SELECT T1.COL1,t1.COL2,T2.COL3 from t1 join t2 on(t1.col2=t2.col2) where t1.col1=1;
外連接就會(huì)發(fā)生不同,所以在外連接中除了連接限制條件外,其他的限制條件所處的文本位置,會(huì)影響最后的結(jié)果
SELECT T1.COL1,t1.COL2,T2.COL3 from t1 left outer join t2 on(t1.col2=t2.col2 and t1.col1=1);
SELECT T1.COL1,t1.COL2,T2.COL3 from t1 left join t2 on(t1.col2=t2.col2) where t1.col1=1;
上面兩條標(biāo)準(zhǔn) sql 語(yǔ)句在 oracle 中用(+)來(lái)表示時(shí),分別可以寫成
SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1,t2 where t1.col2=t2.col2(+) and t1.col1(+)=1;
SELECT T1.COL1,t1.COL2,T2.COL3 FROM T1,t2 where t1.col2=t2.col2(+) and t1.col1=1;
t1.col2(+)= 1 即可表示這個(gè)條件也在連接限制條件內(nèi)。
上述第一條語(yǔ)句的執(zhí)行計(jì)劃會(huì)使用哈希外連接(hash join outer), 而第二條語(yǔ)句會(huì)使用哈希連接(hash join), 即它實(shí)際上時(shí)用等價(jià)的內(nèi)鏈接來(lái)執(zhí)行的
內(nèi)鏈接適用的 natural join 同樣可以用于外連接
三、表連接的方法
1、排序合并連接(sort merge join)
兩個(gè)表各自排序后合并得到結(jié)果集
執(zhí)行效率不如哈希連接,但是比哈希連接適用更大的范圍,因?yàn)楣_B接通常只用于等值連接條件,排序合并連接可以使用不同的連接條件,比如 = = 等等。
并不適合在 OLAP,當(dāng)然如果可以避免排序,也可以用在 OLAP,比如在兩個(gè)表各自的連接列上存在索引
嚴(yán)格意義上排序合并連接并不存在驅(qū)動(dòng)表的概念
2、嵌套循環(huán)連接(nested loops join)
兩個(gè)表連接時(shí)依靠?jī)蓪忧短籽h(huán)(外層循環(huán)和內(nèi)層循環(huán))來(lái)得到結(jié)果集
步驟
a、優(yōu)化器根據(jù)規(guī)則決定驅(qū)動(dòng)表和被驅(qū)動(dòng)表,驅(qū)動(dòng)表用于外層,被驅(qū)動(dòng)表用于內(nèi)層
b、根據(jù)謂語(yǔ)條件訪問(wèn)驅(qū)動(dòng)表,得到結(jié)果集 1
c、遍歷結(jié)果集 1 同時(shí)遍歷被驅(qū)動(dòng)表,即先取出結(jié)果集 1 中的一條記錄,然后根據(jù)這條記錄遍歷被驅(qū)動(dòng)表,找出相匹配的記錄,然后再取出結(jié)果集 1 中的第二條記錄,繼續(xù)遍歷被驅(qū)動(dòng)表,直到結(jié)果集 1 所有記錄取完,即結(jié)果集 1 中有多少條記錄,就會(huì)遍歷循環(huán)多少次被驅(qū)動(dòng)表,就會(huì)有多少次內(nèi)層循環(huán)
嵌套循環(huán)連接的關(guān)鍵點(diǎn)在于結(jié)果集 1 的數(shù)據(jù)量,數(shù)據(jù)量少效率就會(huì)高,同時(shí)這種連接有一個(gè)其他連接沒(méi)有的優(yōu)點(diǎn):可以實(shí)現(xiàn)快速響應(yīng)。即可以快速返回已經(jīng)連接過(guò),且滿足連接條件的記錄,而不用等所有的連接全部完成
oracle 11g 中加入了向量 I /O(vector I/O) 提高嵌套循環(huán)的性能
3、哈希連接(hash join)
兩個(gè)表連接時(shí)通過(guò)哈希運(yùn)算來(lái)得到結(jié)果集
_hash_join_enabled 參數(shù) 默認(rèn)為 true,啟用 hash_join
alter session set _hash_join_enabled =true;
alter session set _hash_join_enabled =false;
use_hash hint 優(yōu)先級(jí)比這個(gè)參數(shù)高
Si Bj Sn Bn(代表磁盤中的 hash partition)
hash 的優(yōu)缺點(diǎn)和適用場(chǎng)景
a、hash 不一定排序,或者說(shuō)大部分都不會(huì)排序
b、驅(qū)動(dòng)表的連接列可選擇性(selectivity) 盡可能好, 因?yàn)檫@會(huì)影響 hash bucket 中的記錄數(shù)
c、只適用于 CBO,且只適用于等值連接
d、適合小表和大表做連接且結(jié)果集較大的情況,小表連接列可選擇性非常好的情況下,哈希連接的執(zhí)行時(shí)間相當(dāng)于大表做全表掃描的時(shí)間
e、兩個(gè)表連接時(shí),做完 hash 后,hash table 能完全在內(nèi)存(PGA)中的話,哈希連接的執(zhí)行效率會(huì)非常高
4、笛卡爾連接(cross join)
兩個(gè)表在做連接時(shí)沒(méi)有任何連接條件的表連接方法,實(shí)際上是一種特殊的合并連接,并不排序(MERGE JOIN CARTESIAN),T1 結(jié)果集為 m,T2 結(jié)果集為 n, 則笛卡爾連接后的記錄數(shù)為 M *N
select t1.col1,t2.col3 from t1,t2;
標(biāo)準(zhǔn) sql : select t1.col1,t2.col3 from t1 cross join t2;
笛卡爾連接一般是不好的,往往是因?yàn)槁懥诉B接條件或者用了 ordered hint,而 sql 文本上相鄰的倆個(gè)表有沒(méi)有直接的關(guān)聯(lián)條件造成的,還有可能是統(tǒng)計(jì)信息不準(zhǔn)確造成。除非是刻意的,比如為了避免多次的大表全表掃描才會(huì)使用這種連接。
四、反連接(anti join)
結(jié)果集 1,結(jié)果集 2,滿足 t1.col2=t2.col2 的都會(huì)被去除,只返回不滿足連接條件的值
select * from t1 where col2 not in(select col2 from t2);
select * from t1 where col2 all(select col2 from t2);
select * from t1 where not exists(select 1 from t2 where col2=t1.col2);
當(dāng) t1,t2 沒(méi)有 null 值時(shí),上面三條語(yǔ)句結(jié)果相同
當(dāng)有 null 時(shí),結(jié)果會(huì)不一樣
not in 和 all 對(duì) null 敏感,當(dāng)他們后面的子查詢或常量集合有 null,則整個(gè) sql 的執(zhí)行結(jié)果就是 null
not exists 對(duì) null 不敏感,不影響執(zhí)行結(jié)果
五、半連接(semi join)
t1,t2 連接時(shí),驅(qū)動(dòng)表 t1, 被驅(qū)動(dòng)表 t2, 即使 t2 中滿足連接條件 t1.col2=t2.col2 有多條記錄,也只會(huì)返回第一條記錄,即半連接時(shí)特殊的內(nèi)連接,實(shí)際具有去重的作用。當(dāng)子查詢展開(kāi)時(shí),oracle 通常會(huì)把 where 后的條件 =any,exist,in 等查詢轉(zhuǎn)換為對(duì)應(yīng)的半連接。
select * from t1 where col2 in(select col2 from t2);
select * from t1 where col2= any(select col2 from t2);
select * from t1 where exists(select col2 from t2 where col2=t1.col2);
六、星形連接(star join)
通常用于數(shù)據(jù)倉(cāng)庫(kù),既不是連接類型,也不是連接方法,他是一個(gè)事實(shí)表(fact table) 與多個(gè)維度表(dimension table) 之間的連接,基本上事實(shí)表的外鍵列對(duì)應(yīng)各維度表的主鍵列,事實(shí)表是張大表,后面章節(jié)會(huì)詳細(xì)描述這個(gè)連接
感謝各位的閱讀,以上就是“oracle 表連接的方法和類型有哪些”的內(nèi)容了,經(jīng)過(guò)本文的學(xué)習(xí)后,相信大家對(duì) oracle 表連接的方法和類型有哪些這一問(wèn)題有了更深刻的體會(huì),具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是丸趣 TV,丸趣 TV 小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章,歡迎關(guān)注!