溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇內(nèi)容介紹了“數(shù)據(jù)庫(kù)中外連接有OR關(guān)聯(lián)條件只能走NL優(yōu)化的方法是什么”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
測(cè)試數(shù)據(jù)
drop table t1 purge; drop table t2 purge; create table t1 (id int,name varchar2(10),age int); insert into t1 values(1,'a',1); insert into t1 values(2,'b',2); insert into t1 values(3,'c',5); insert into t1 values(4,'d',1); insert into t1 values(5,'e',3); insert into t1 values(6,'f',6); create table t2 (id int,name varchar2(10)); insert into t2 values(1,'a'); insert into t2 values(2,'b'); insert into t2 values(3,'c'); insert into t2 values(1,'y');
外連接有OR關(guān)聯(lián)條件只能走NL，若驅(qū)動(dòng)表的結(jié)果集較大，則會(huì)產(chǎn)生大量的關(guān)聯(lián)，會(huì)產(chǎn)生性能問(wèn)題，需要進(jìn)行優(yōu)化。
在兩個(gè)表做外連接時(shí)存在幾種情況：
1、在進(jìn)行外連接時(shí)，使用nl，此時(shí)主表被固定成驅(qū)動(dòng)表，無(wú)法通過(guò)hint進(jìn)行驅(qū)動(dòng)表的調(diào)整
2、在進(jìn)行外連接時(shí)，使用hash，可以通過(guò)hint調(diào)整驅(qū)動(dòng)表和被驅(qū)動(dòng)表
針對(duì)外連接的情況做以下的實(shí)驗(yàn)：
1、當(dāng)執(zhí)行計(jì)劃是nl，t1是驅(qū)動(dòng)表(主表)，t2是被驅(qū)動(dòng)表，調(diào)整t2為驅(qū)動(dòng)表，t1為被驅(qū)動(dòng)表。
2、當(dāng)執(zhí)行計(jì)劃是nl，t1是驅(qū)動(dòng)表(主表)，t2是被驅(qū)動(dòng)表，調(diào)整執(zhí)行計(jì)劃為hash。
3、當(dāng)執(zhí)行計(jì)劃是hash，t1是驅(qū)動(dòng)表(主表)，t2是被驅(qū)動(dòng)表，調(diào)整t2為驅(qū)動(dòng)表，t1為被驅(qū)動(dòng)表。
4、當(dāng)執(zhí)行計(jì)劃是hash，t1是驅(qū)動(dòng)表(主表)，t2是被驅(qū)動(dòng)表，調(diào)整執(zhí)行計(jì)劃為nl。
5、當(dāng)外連接有OR關(guān)聯(lián)條件(T1.ID = T2.ID OR T1.AGE = T2.ID)，進(jìn)行等價(jià)改寫(xiě)
執(zhí)行計(jì)劃是nl的情況
有如下SQL：
SELECT T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
ORDER BY 1;
T1_ID T1_NAME T1_AGE T2_ID T2_NAME
---------- ---------- ---------- ---------- ----------
1 a 1 1 a
2 b 2 2 b
3 c 5 3 c
4 d 1
5 e 3
6 f 6
執(zhí)行計(jì)劃：
Plan hash value: 3645848104
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 11 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 6 |00:00:00.01 | 11 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 |00:00:00.01 | 11 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 3 |00:00:00.01 | 4 | | | |
|* 5 | INDEX RANGE SCAN | IDX_ID_T2_01 | 6 | 1 | 3 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")通過(guò)執(zhí)行計(jì)劃可以看到，走了nl，并且t1是驅(qū)動(dòng)表。
1、當(dāng)執(zhí)行計(jì)劃是nl，t1是驅(qū)動(dòng)表(主表)，t2是被驅(qū)動(dòng)表，調(diào)整t2為驅(qū)動(dòng)表，t1為被驅(qū)動(dòng)表。
在內(nèi)連接中，可以實(shí)現(xiàn)驅(qū)動(dòng)表和被驅(qū)動(dòng)表的調(diào)整，但是在外連接中不能調(diào)整驅(qū)動(dòng)表的順序
SELECT /*+ leading(t2 t1) use_nl(t1)*/T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
ORDER BY 1;
Plan hash value: 109855138
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 11 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 6 |00:00:00.01 | 11 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 |00:00:00.01 | 11 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 3 |00:00:00.01 | 4 | | | |
|* 5 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 1 | 3 |00:00:00.01 | 3 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")通過(guò)執(zhí)行計(jì)劃可以看到，驅(qū)動(dòng)表還是t1，并沒(méi)有改變執(zhí)行順序，因此在執(zhí)行計(jì)劃是nl的外連接中無(wú)法進(jìn)行驅(qū)動(dòng)表和
被驅(qū)動(dòng)表的調(diào)整。
不能調(diào)整的原因：
在進(jìn)行外連接時(shí)，t1作為主表，左外連接t2，因此需要返回t1的全部數(shù)據(jù)。嵌套循環(huán)需要傳值，主表傳值給從表之后，
如果發(fā)現(xiàn)從表沒(méi)有關(guān)聯(lián)上，直接顯示為 NULL 即可；
但是如果是從表傳值給主表，沒(méi)關(guān)聯(lián)上的數(shù)據(jù)不能傳值給主表，不可能傳 NULL 給主表，所以兩表關(guān)聯(lián)是外連接的時(shí)候，
走嵌套循環(huán)驅(qū)動(dòng)表只能固定為主表。
2、當(dāng)執(zhí)行計(jì)劃是nl，t1是驅(qū)動(dòng)表(主表)，t2是被驅(qū)動(dòng)表，調(diào)整執(zhí)行計(jì)劃為hash。
想辦法調(diào)整為hash
使用hint：use_hash()
驅(qū)動(dòng)表：t1
被驅(qū)動(dòng)表：t2
SELECT /*+ leading(t2 t1) use_hash(t1) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
ORDER BY 1;
Plan hash value: 109855138
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 11 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 6 |00:00:00.01 | 11 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 |00:00:00.01 | 11 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 3 |00:00:00.01 | 4 | | | |
|* 5 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 1 | 3 |00:00:00.01 | 3 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")此時(shí)的hint未生效，走了最初的nl連接。
嘗試使用其他hint
SWAP_JOIN_INPUTS ：說(shuō)明連接當(dāng)中誰(shuí)做內(nèi)建表（驅(qū)動(dòng)表）
NO_SWAP_JOIN_INPUTS ：說(shuō)明連接中誰(shuí)做探測(cè)表（被驅(qū)動(dòng)表）
SELECT /*+ leading(t2 t1) use_hash(t1) no_swap_join_inputs(t1) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
ORDER BY 1;
Plan hash value: 109855138
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 11 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 6 |00:00:00.01 | 11 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 |00:00:00.01 | 11 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 3 |00:00:00.01 | 4 | | | |
|* 5 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 1 | 3 |00:00:00.01 | 3 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")此時(shí)的hint未生效，走了最初的nl連接。
原因和走nl，不能調(diào)整驅(qū)動(dòng)表和被驅(qū)動(dòng)表的原理一致，只可以改變表的連接方式，但是不能改變表的訪問(wèn)順序。
3、當(dāng)執(zhí)行計(jì)劃是hash，t1是驅(qū)動(dòng)表(主表)，t2是被驅(qū)動(dòng)表，調(diào)整t2為驅(qū)動(dòng)表，t1為被驅(qū)動(dòng)表。
想辦法調(diào)整表的訪問(wèn)順序
使用hint：use_hash()
驅(qū)動(dòng)表：t2
被驅(qū)動(dòng)表：t1
SELECT /*+ leading(t2 t1) use_hash(t1) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
ORDER BY 1;
Plan hash value: 2391546071
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 14 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 6 |00:00:00.01 | 14 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN OUTER | | 1 | 6 | 6 |00:00:00.01 | 14 | 1753K| 1753K| 920K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")通過(guò)執(zhí)行計(jì)劃可以看到，驅(qū)動(dòng)表還是t1，并沒(méi)有改變執(zhí)行順序。
需要在加上一個(gè)hint
SWAP_JOIN_INPUTS ：說(shuō)明連接當(dāng)中誰(shuí)做內(nèi)建表（驅(qū)動(dòng)表）
NO_SWAP_JOIN_INPUTS ：說(shuō)明連接中誰(shuí)做探測(cè)表（被驅(qū)動(dòng)表）
SELECT /*+ leading(t2 t1) use_hash(t1) swap_join_inputs(t2) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
ORDER BY 1;
Plan hash value: 2146067096
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 14 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 6 |00:00:00.01 | 14 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 6 | 6 |00:00:00.01 | 14 | 2061K| 2061K| 872K (0)|
| 3 | TABLE ACCESS FULL | T2 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")通過(guò)執(zhí)行計(jì)劃可以看到，此時(shí)驅(qū)動(dòng)表已經(jīng)變成了t2，被驅(qū)動(dòng)表變成了t1，同時(shí)可以看到id=2的操作，
從原來(lái)的HASH JOIN OUTER 變成了HASH JOIN RIGHT OUTER，這部分是等價(jià)的，
相當(dāng)于t1左外連接t2改寫(xiě)為t2右外連接t1。
SELECT /*+ leading(t2 t1) use_hash(t1) no_swap_join_inputs(t1) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
ORDER BY 1;
Plan hash value: 2391546071
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 14 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 6 |00:00:00.01 | 14 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN OUTER | | 1 | 6 | 6 |00:00:00.01 | 14 | 1753K| 1753K| 886K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")此時(shí)hint為生效，還是原來(lái)的執(zhí)行計(jì)劃。
4、當(dāng)執(zhí)行計(jì)劃是hash，t1是驅(qū)動(dòng)表(主表)，t2是被驅(qū)動(dòng)表，調(diào)整執(zhí)行計(jì)劃為nl。
把hash調(diào)整為nl
驅(qū)動(dòng)表：t1
被驅(qū)動(dòng)表：t2
t2的id創(chuàng)建索引
create index idx_id_t2 on t2(id);
SELECT T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
ORDER BY 1;
Plan hash value: 109855138
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 11 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 6 |00:00:00.01 | 11 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 |00:00:00.01 | 11 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 3 |00:00:00.01 | 4 | | | |
|* 5 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 1 | 3 |00:00:00.01 | 3 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")執(zhí)行計(jì)劃中已經(jīng)從hash變?yōu)閚l，并且t1是驅(qū)動(dòng)表，t2是被驅(qū)動(dòng)表
把hash調(diào)整為nl
驅(qū)動(dòng)表：t2
被驅(qū)動(dòng)表：t1
t1的id創(chuàng)建索引
create index idx_id_t1 on t1(id);
SELECT /*+ leading(t2 t1) use_nl(t1) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
ORDER BY 1;
Plan hash value: 109855138
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 11 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 6 |00:00:00.01 | 11 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 |00:00:00.01 | 11 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 3 |00:00:00.01 | 4 | | | |
|* 5 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 1 | 3 |00:00:00.01 | 3 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")通過(guò)執(zhí)行計(jì)劃可以看到，驅(qū)動(dòng)表還是t1，并沒(méi)有改變執(zhí)行順序。
原因和走nl，不能調(diào)整驅(qū)動(dòng)表和被驅(qū)動(dòng)表的原理一致，只可以改變表的連接方式，但是不能改變表的訪問(wèn)順序。
5、當(dāng)外連接有OR關(guān)聯(lián)條件，進(jìn)行等價(jià)改寫(xiě)(2)
SELECT T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON (T1.ID = T2.ID OR T1.AGE = T2.ID)
ORDER BY 1;
T1_ID T1_NAME T1_AGE T2_ID T2_NAME
---------- ---------- ---------- ---------- ----------
1 a 1 1 a
2 b 2 2 b
3 c 5 3 c
4 d 1 1 a
5 e 3 3 c
6 f 6
6 rows selected.
Plan hash value: 3004654521
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 49 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 6 |00:00:00.01 | 49 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 |00:00:00.01 | 49 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | VIEW | | 6 | 1 | 5 |00:00:00.01 | 42 | | | |
|* 5 | TABLE ACCESS FULL| T2 | 6 | 1 | 5 |00:00:00.01 | 42 | | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("T1"."ID"="T2"."ID" OR "T1"."AGE"="T2"."ID"))T1作為主表和T2做外連接，需要返回T1的全部數(shù)據(jù)以及滿足條件的T2記錄，還有不滿足條件的T2的結(jié)果null。
當(dāng)使用了OR，則表示只要滿足其中的一個(gè)條件即可返回T1和T2的記錄。
假設(shè)T1和T2連接時(shí)是從第一行記錄開(kāi)始： 當(dāng)T1拿出第一行記錄的id和age的值傳給T2表，和T2表的第一行記錄進(jìn)行匹配， 在這里有三種情況： 1、如果發(fā)現(xiàn)T1的id值和T2表的id的值相等，但是T1的age值和T2表的id的值不相等，那么返回T1的記錄和T2的記錄，第一行的記錄； 2、如果發(fā)現(xiàn)T1的age值和T2表的id的值相等，但是T1的id值和T2表的id的值不相等，那么也返回T1的記錄和T2的記錄，第一行的記錄； 3、如果發(fā)現(xiàn)T1的id值以及age值和T2表的id的值都相等，那么也返回T1的記錄和T2的記錄，第一行的記錄； 這三種情況的結(jié)果就是要么返回一條記錄，要么都不滿足的情況下T2返回null 當(dāng)?shù)谝恍杏涗浧ヅ渫炅?，接下去該?duì)T1的第二行記錄和T2的第二行記錄進(jìn)行匹配，匹配的方法和情況還是和上述的方法一致。 直到把T1的所有記錄都匹配一遍，才最終的得到滿足條件的記錄和不滿足條件的T2的null。 因此在這種情況下，需要一行一行的去匹配數(shù)據(jù)，所以優(yōu)化器選擇了使用nl，需要嵌套循環(huán)的匹配數(shù)據(jù)。
這時(shí)候的執(zhí)行計(jì)劃肯定是有問(wèn)題的：
1、被驅(qū)動(dòng)表是全表掃描，連接列沒(méi)有索引，t1傳出一條數(shù)據(jù)，t2就需要全表掃描一次。
2、一般來(lái)說(shuō)，走nl是小表在前，大表在后，但是在外連接中，走了nl，或者確定了主表，那么他就一定是驅(qū)動(dòng)表，
這里的主表可以是一個(gè)表，也可以是一個(gè)過(guò)濾完的結(jié)果集，因此當(dāng)主表的結(jié)果集很大的時(shí)候，驅(qū)動(dòng)表就需要被驅(qū)動(dòng)很多次，
做了大量的join操作，耗費(fèi)很多的資源。
幾種情況：
t1是小表，t2是大表，但是t2列沒(méi)有索引，都是全表掃描；
t1是小表，t2是小表，但是t2列沒(méi)有索引，都是全表掃描；
t1是大表，t2是大表，但是t2列沒(méi)有索引，都是全表掃描；
t1是大表，t2是小表，但是t2列沒(méi)有索引，都是全表掃描；
以上的操作都是有問(wèn)題，走的是nl，但是被驅(qū)動(dòng)表都是全表掃描。
還有其他情況，t2表的連接列有索引
t1是小表，t2是大表，但是t2列有索引；
t1是小表，t2是小表，但是t2列有索引；
t1是大表，t2是大表，但是t2列有索引；
t1是大表，t2是小表，但是t2列有索引；
以上的操作相比較全表掃描而言性能有所提高，但是也是存在大量的join。
當(dāng)t2的id列有索引時(shí)
create index idx_id_t2 on t2(id);
SELECT T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON (T1.ID = T2.ID OR T1.AGE = T2.ID)
ORDER BY 1;
Plan hash value: 2234182087
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 24 | | | |
| 1 | SORT ORDER BY | | 1 | 12 | 6 |00:00:00.01 | 24 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 12 | 6 |00:00:00.01 | 24 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | VIEW | | 6 | 2 | 5 |00:00:00.01 | 17 | | | |
| 5 | CONCATENATION | | 6 | | 5 |00:00:00.01 | 17 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 4 |00:00:00.01 | 10 | | | |
|* 7 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 2 | 4 |00:00:00.01 | 6 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 9 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 2 | 1 |00:00:00.01 | 6 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T1"."AGE"="T2"."ID")
9 - access("T1"."ID"="T2"."ID")
filter(LNNVL("T1"."AGE"="T2"."ID"))因?yàn)檫B接條件都是對(duì)t2的id進(jìn)行關(guān)聯(lián)，在t2的連接條件上有索引時(shí)，會(huì)使用索引，但是會(huì)進(jìn)行兩次索引掃描，然后回表，
然后把這個(gè)結(jié)果集作為一個(gè)視圖。
t1給一條記錄，則掃描一次視圖，這樣也是有問(wèn)題的。
使用上述操作時(shí)存在以下問(wèn)題：
1、訪問(wèn)方式被固定，只能使用nl，不管被驅(qū)動(dòng)表的連接列是否有索引
2、當(dāng)驅(qū)動(dòng)表很大，被驅(qū)動(dòng)表很小，使用nl的效率很低，被驅(qū)動(dòng)表需要訪問(wèn)t1的行記錄數(shù)(結(jié)果集)
優(yōu)化思路：
1、調(diào)整驅(qū)動(dòng)表和被驅(qū)動(dòng)表的順序
2、使用hash
1、調(diào)整驅(qū)動(dòng)表和被驅(qū)動(dòng)表的順序
SELECT /*+ leading(t2 t1) use_nl(t1) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON (T1.ID = T2.ID OR T1.AGE = T2.ID)
ORDER BY 1;
Plan hash value: 2234182087
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 24 | | | |
| 1 | SORT ORDER BY | | 1 | 12 | 6 |00:00:00.01 | 24 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 12 | 6 |00:00:00.01 | 24 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | VIEW | | 6 | 2 | 5 |00:00:00.01 | 17 | | | |
| 5 | CONCATENATION | | 6 | | 5 |00:00:00.01 | 17 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 4 |00:00:00.01 | 10 | | | |
|* 7 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 2 | 4 |00:00:00.01 | 6 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 9 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 2 | 1 |00:00:00.01 | 6 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T1"."AGE"="T2"."ID")
9 - access("T1"."ID"="T2"."ID")
filter(LNNVL("T1"."AGE"="T2"."ID"))原因：nl的外連接無(wú)法更改驅(qū)動(dòng)表被驅(qū)動(dòng)表。
2、使用hash
SELECT /*+ leading(t1 t2) use_hash(t2) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON (T1.ID = T2.ID OR T1.AGE = T2.ID)
ORDER BY 1;
Plan hash value: 2234182087
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 24 | | | |
| 1 | SORT ORDER BY | | 1 | 12 | 6 |00:00:00.01 | 24 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 12 | 6 |00:00:00.01 | 24 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | VIEW | | 6 | 2 | 5 |00:00:00.01 | 17 | | | |
| 5 | CONCATENATION | | 6 | | 5 |00:00:00.01 | 17 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 4 |00:00:00.01 | 10 | | | |
|* 7 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 2 | 4 |00:00:00.01 | 6 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 9 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 2 | 1 |00:00:00.01 | 6 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T1"."AGE"="T2"."ID")
9 - access("T1"."ID"="T2"."ID")
filter(LNNVL("T1"."AGE"="T2"."ID"))加hash的hint
SWAP_JOIN_INPUTS ：說(shuō)明連接當(dāng)中誰(shuí)做內(nèi)建表（驅(qū)動(dòng)表）
NO_SWAP_JOIN_INPUTS ：說(shuō)明連接中誰(shuí)做探測(cè)表（被驅(qū)動(dòng)表）
SELECT /*+ leading(t1 t2) use_hash(t2) swap_join_inputs(t1) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON (T1.ID = T2.ID OR T1.AGE = T2.ID)
ORDER BY 1;
Plan hash value: 2234182087
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 24 | | | |
| 1 | SORT ORDER BY | | 1 | 12 | 6 |00:00:00.01 | 24 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 12 | 6 |00:00:00.01 | 24 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | VIEW | | 6 | 2 | 5 |00:00:00.01 | 17 | | | |
| 5 | CONCATENATION | | 6 | | 5 |00:00:00.01 | 17 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 4 |00:00:00.01 | 10 | | | |
|* 7 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 2 | 4 |00:00:00.01 | 6 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 9 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 2 | 1 |00:00:00.01 | 6 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T1"."AGE"="T2"."ID")
9 - access("T1"."ID"="T2"."ID")
filter(LNNVL("T1"."AGE"="T2"."ID"))
SELECT /*+ leading(t1 t2) use_hash(t2) no_swap_join_inputs(t2) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON (T1.ID = T2.ID OR T1.AGE = T2.ID)
ORDER BY 1;
Plan hash value: 2234182087
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 24 | | | |
| 1 | SORT ORDER BY | | 1 | 12 | 6 |00:00:00.01 | 24 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 12 | 6 |00:00:00.01 | 24 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | VIEW | | 6 | 2 | 5 |00:00:00.01 | 17 | | | |
| 5 | CONCATENATION | | 6 | | 5 |00:00:00.01 | 17 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 4 |00:00:00.01 | 10 | | | |
|* 7 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 2 | 4 |00:00:00.01 | 6 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 9 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 2 | 1 |00:00:00.01 | 6 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T1"."AGE"="T2"."ID")
9 - access("T1"."ID"="T2"."ID")
filter(LNNVL("T1"."AGE"="T2"."ID"))無(wú)法把執(zhí)行計(jì)劃調(diào)整為hash。
最終思路：
需要進(jìn)行等價(jià)改寫(xiě)，使得這樣的查詢執(zhí)行計(jì)劃不走nl，或者可以更改驅(qū)動(dòng)表(不可能，前面提過(guò)，nl的外連接無(wú)法更改驅(qū)動(dòng)表)。
因此只慮等價(jià)改寫(xiě)，用來(lái)消除or的影響。
在進(jìn)行等價(jià)改寫(xiě)時(shí)，又分為兩種情況：
1、t2的id字段沒(méi)有重復(fù)值
2、t2的id字段有重復(fù)值
當(dāng)t2的id字段沒(méi)有重復(fù)值，進(jìn)行等價(jià)改寫(xiě)(感謝郭老師)：
SELECT *
FROM (SELECT T.*
,ROW_NUMBER() OVER(PARTITION BY T.T1_RID ORDER BY T.T2_ID) RN
FROM (SELECT T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
,T1.ROWID T1_RID
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
UNION ALL
SELECT T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
,T1.ROWID T1_RID
FROM T1
LEFT JOIN T2
ON T1.AGE = T2.ID) T)
WHERE RN = 1
order by 1;
T1_ID T1_NAME T1_AGE T2_ID T2_NAME T1_RID RN
---------- ---------- ---------- ---------- ---------- ------------------ ----------
1 a 1 1 a AAAVuJAAEAAAByUAAA 1
2 b 2 2 b AAAVuJAAEAAAByUAAB 1
3 c 5 3 c AAAVuJAAEAAAByUAAC 1
4 d 1 1 a AAAVuJAAEAAAByUAAD 1
5 e 3 3 c AAAVuJAAEAAAByUAAE 1
6 f 6 AAAVuJAAEAAAByUAAF 1
6 rows selected.
Plan hash value: 3180408145
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 28 | | | |
| 1 | SORT ORDER BY | | 1 | 12 | 6 |00:00:00.01 | 28 | 2048 | 2048 | 2048 (0)|
|* 2 | VIEW | | 1 | 12 | 6 |00:00:00.01 | 28 | | | |
|* 3 | WINDOW SORT PUSHED RANK| | 1 | 12 | 12 |00:00:00.01 | 28 | 2048 | 2048 | 2048 (0)|
| 4 | VIEW | | 1 | 12 | 12 |00:00:00.01 | 28 | | | |
| 5 | UNION-ALL | | 1 | | 12 |00:00:00.01 | 28 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 6 | 6 |00:00:00.01 | 14 | 1321K| 1321K| 939K (0)|
| 7 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 8 | TABLE ACCESS FULL | T2 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
|* 9 | HASH JOIN OUTER | | 1 | 6 | 6 |00:00:00.01 | 14 | 1321K| 1321K| 939K (0)|
| 10 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 11 | TABLE ACCESS FULL | T2 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."T1_RID" ORDER BY "T"."T2_ID")<=1)
6 - access("T1"."ID"="T2"."ID")
9 - access("T1"."AGE"="T2"."ID")
當(dāng)t2的id列有索引時(shí)
create index idx_id_t2 on t2(id);
SELECT *
FROM (SELECT T.*
,ROW_NUMBER() OVER(PARTITION BY T.T1_RID ORDER BY T.T2_ID) RN
FROM (SELECT T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
,T1.ROWID T1_RID
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
UNION ALL
SELECT T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
,T1.ROWID T1_RID
FROM T1
LEFT JOIN T2
ON T1.AGE = T2.ID) T)
WHERE RN = 1
order by 1;
Plan hash value: 1354803237
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 25 | | | |
| 1 | SORT ORDER BY | | 1 | 12 | 6 |00:00:00.01 | 25 | 2048 | 2048 | 2048 (0)|
|* 2 | VIEW | | 1 | 12 | 6 |00:00:00.01 | 25 | | | |
|* 3 | WINDOW SORT PUSHED RANK | | 1 | 12 | 12 |00:00:00.01 | 25 | 2048 | 2048 | 2048 (0)|
| 4 | VIEW | | 1 | 12 | 12 |00:00:00.01 | 25 | | | |
| 5 | UNION-ALL | | 1 | | 12 |00:00:00.01 | 25 | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 6 | 6 |00:00:00.01 | 11 | | | |
| 7 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 3 |00:00:00.01 | 4 | | | |
|* 9 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 1 | 3 |00:00:00.01 | 3 | | | |
|* 10 | HASH JOIN OUTER | | 1 | 6 | 6 |00:00:00.01 | 14 | 1321K| 1321K| 897K (0)|
| 11 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 12 | TABLE ACCESS FULL | T2 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."T1_RID" ORDER BY "T"."T2_ID")<=1)
9 - access("T1"."ID"="T2"."ID")
10 - access("T1"."AGE"="T2"."ID")
上面的查詢使用了索引，但是下面的查詢并未用到索引，可以使用hint指定使用索引
SELECT *
FROM (SELECT T.*
,ROW_NUMBER() OVER(PARTITION BY T.T1_RID ORDER BY T.T2_ID) RN
FROM (SELECT T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
,T1.ROWID T1_RID
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
UNION ALL
SELECT /*+ leading(t1 t2) use_nl(t2) */T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
,T1.ROWID T1_RID
FROM T1
LEFT JOIN T2
ON T1.AGE = T2.ID) T)
WHERE RN = 1
order by 1;
Plan hash value: 4092066186
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 22 | | | |
| 1 | SORT ORDER BY | | 1 | 12 | 6 |00:00:00.01 | 22 | 2048 | 2048 | 2048 (0)|
|* 2 | VIEW | | 1 | 12 | 6 |00:00:00.01 | 22 | | | |
|* 3 | WINDOW SORT PUSHED RANK | | 1 | 12 | 12 |00:00:00.01 | 22 | 2048 | 2048 | 2048 (0)|
| 4 | VIEW | | 1 | 12 | 12 |00:00:00.01 | 22 | | | |
| 5 | UNION-ALL | | 1 | | 12 |00:00:00.01 | 22 | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 6 | 6 |00:00:00.01 | 11 | | | |
| 7 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 3 |00:00:00.01 | 4 | | | |
|* 9 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 1 | 3 |00:00:00.01 | 3 | | | |
| 10 | NESTED LOOPS OUTER | | 1 | 6 | 6 |00:00:00.01 | 11 | | | |
| 11 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 1 | 4 |00:00:00.01 | 4 | | | |
|* 13 | INDEX RANGE SCAN | IDX_ID_T2 | 6 | 1 | 4 |00:00:00.01 | 3 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."T1_RID" ORDER BY "T"."T2_ID")<=1)
9 - access("T1"."ID"="T2"."ID")
13 - access("T1"."AGE"="T2"."ID")通過(guò)執(zhí)行計(jì)劃可以看到，不走nl，都走了hash，并且通過(guò)Starts列可以看到，對(duì)每個(gè)表的訪問(wèn)次數(shù)都是1，
達(dá)到了通過(guò)改寫(xiě)SQL把nl調(diào)整為hash的效果。
最終的優(yōu)化效果，邏輯讀由49降到了22。
當(dāng)t2的id字段有重復(fù)值，進(jìn)行等價(jià)改寫(xiě)：
SQL> select * from t1;
ID NAME AGE
---------- ---------- ----------
1 a 1
2 b 2
3 c 5
4 d 1
5 e 3
6 f 6
6 rows selected.
Elapsed: 00:00:00.01
SQL> select * from t2;
ID NAME
---------- ----------
1 a
2 b
3 c
1 y
SELECT T1.ID T1_ID
,T1.NAME T1_NAME
,T1.AGE T1_AGE
,T2.ID T2_ID
,T2.NAME T2_NAME
FROM T1
LEFT JOIN T2
ON (T1.ID = T2.ID OR T1.AGE = T2.ID)
ORDER BY 1;
T1_ID T1_NAME T1_AGE T2_ID T2_NAME
---------- ---------- ---------- ---------- ----------
1 a 1 1 a
1 a 1 1 y
2 b 2 2 b
3 c 5 3 c
4 d 1 1 a
4 d 1 1 y
5 e 3 3 c
6 f 6
8 rows selected.
Plan hash value: 3004654521
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 |00:00:00.01 | 49 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 8 |00:00:00.01 | 49 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 6 | 8 |00:00:00.01 | 49 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
| 4 | VIEW | | 6 | 1 | 7 |00:00:00.01 | 42 | | | |
|* 5 | TABLE ACCESS FULL| T2 | 6 | 1 | 7 |00:00:00.01 | 42 | | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("T1"."ID"="T2"."ID" OR "T1"."AGE"="T2"."ID"))
等價(jià)改寫(xiě)(感謝劉老師指導(dǎo))
WITH TMP_A AS
(SELECT ID
,NAME
,AGE
,0 AS FLAG
FROM T1
UNION ALL
SELECT AGE
,NAME
,ID
,NULL
FROM T1
WHERE LNNVL(ID = AGE)),
TMP_B AS
(SELECT A.ID
,A.NAME
,A.AGE
,A.FLAG
,B.ID AS BID
,B.NAME AS BNAME
FROM TMP_A A
LEFT JOIN T2 B
ON A.ID = B.ID),
TMP_C AS
(SELECT NVL2(FLAG, ID, AGE) AS ID
,NAME
,NVL2(FLAG, AGE, ID) AS AGE
,BID
,BNAME
,FLAG
,DENSE_RANK() OVER(PARTITION BY NVL2(FLAG, ID, AGE), NAME, NVL2(FLAG, AGE, ID) ORDER BY NVL2(BID, 1, NULL) NULLS LAST) AS DRN
FROM TMP_B)
SELECT ID
,NAME
,AGE
,BID
,BNAME --,drn,flag
FROM TMP_C
WHERE DRN = 1
AND (FLAG IS NOT NULL OR BID IS NOT NULL)
ORDER BY 1
,2
,3
,4
,5;
Plan hash value: 1011965060
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 |00:00:00.01 | 21 | | | |
| 1 | SORT ORDER BY | | 1 | 12 | 8 |00:00:00.01 | 21 | 2048 | 2048 | 2048 (0)|
|* 2 | VIEW | | 1 | 12 | 8 |00:00:00.01 | 21 | | | |
|* 3 | WINDOW SORT PUSHED RANK| | 1 | 12 | 11 |00:00:00.01 | 21 | 2048 | 2048 | 2048 (0)|
|* 4 | HASH JOIN OUTER | | 1 | 12 | 11 |00:00:00.01 | 21 | 1645K| 1645K| 908K (0)|
| 5 | VIEW | | 1 | 9 | 9 |00:00:00.01 | 14 | | | |
| 6 | UNION-ALL | | 1 | | 9 |00:00:00.01 | 14 | | | |
| 7 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |00:00:00.01 | 7 | | | |
|* 8 | TABLE ACCESS FULL | T1 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
| 9 | TABLE ACCESS FULL | T2 | 1 | 4 | 4 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DRN"=1 AND ("FLAG" IS NOT NULL OR "BID" IS NOT NULL)))
3 - filter(DENSE_RANK() OVER ( PARTITION BY NVL2("A"."FLAG","A"."ID","A"."AGE"),"A"."NAME",NVL2("A"."FLAG","
A"."AGE","A"."ID") ORDER BY NVL2("B"."ID",1,NULL))<=1)
4 - access("A"."ID"="B"."ID")
8 - filter(LNNVL("ID"="AGE"))通過(guò)執(zhí)行計(jì)劃可以看到，不走nl，都走了hash，并且通過(guò)Starts列可以看到，對(duì)每個(gè)表的訪問(wèn)次數(shù)都是1，
達(dá)到了通過(guò)改寫(xiě)SQL把nl調(diào)整為hash的效果。
最終的優(yōu)化效果，邏輯讀由49降到了21。
“數(shù)據(jù)庫(kù)中外連接有OR關(guān)聯(lián)條件只能走NL優(yōu)化的方法是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼