溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“數據庫中外連接有OR關聯條件只能走NL優化的方法是什么”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
測試數據
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關聯條件只能走NL，若驅動表的結果集較大，則會產生大量的關聯，會產生性能問題，需要進行優化。
在兩個表做外連接時存在幾種情況：
1、在進行外連接時，使用nl，此時主表被固定成驅動表，無法通過hint進行驅動表的調整
2、在進行外連接時，使用hash，可以通過hint調整驅動表和被驅動表
針對外連接的情況做以下的實驗：
1、當執行計劃是nl，t1是驅動表(主表)，t2是被驅動表，調整t2為驅動表，t1為被驅動表。
2、當執行計劃是nl，t1是驅動表(主表)，t2是被驅動表，調整執行計劃為hash。
3、當執行計劃是hash，t1是驅動表(主表)，t2是被驅動表，調整t2為驅動表，t1為被驅動表。
4、當執行計劃是hash，t1是驅動表(主表)，t2是被驅動表，調整執行計劃為nl。
5、當外連接有OR關聯條件(T1.ID = T2.ID OR T1.AGE = T2.ID)，進行等價改寫
執行計劃是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
執行計劃：
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")通過執行計劃可以看到，走了nl，并且t1是驅動表。
1、當執行計劃是nl，t1是驅動表(主表)，t2是被驅動表，調整t2為驅動表，t1為被驅動表。
在內連接中，可以實現驅動表和被驅動表的調整，但是在外連接中不能調整驅動表的順序
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")通過執行計劃可以看到，驅動表還是t1，并沒有改變執行順序，因此在執行計劃是nl的外連接中無法進行驅動表和
被驅動表的調整。
不能調整的原因：
在進行外連接時，t1作為主表，左外連接t2，因此需要返回t1的全部數據。嵌套循環需要傳值，主表傳值給從表之后，
如果發現從表沒有關聯上，直接顯示為 NULL 即可；
但是如果是從表傳值給主表，沒關聯上的數據不能傳值給主表，不可能傳 NULL 給主表，所以兩表關聯是外連接的時候，
走嵌套循環驅動表只能固定為主表。
2、當執行計劃是nl，t1是驅動表(主表)，t2是被驅動表，調整執行計劃為hash。
想辦法調整為hash
使用hint：use_hash()
驅動表：t1
被驅動表：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")此時的hint未生效，走了最初的nl連接。
嘗試使用其他hint
SWAP_JOIN_INPUTS ：說明連接當中誰做內建表（驅動表）
NO_SWAP_JOIN_INPUTS ：說明連接中誰做探測表（被驅動表）
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")此時的hint未生效，走了最初的nl連接。
原因和走nl，不能調整驅動表和被驅動表的原理一致，只可以改變表的連接方式，但是不能改變表的訪問順序。
3、當執行計劃是hash，t1是驅動表(主表)，t2是被驅動表，調整t2為驅動表，t1為被驅動表。
想辦法調整表的訪問順序
使用hint：use_hash()
驅動表：t2
被驅動表：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")通過執行計劃可以看到，驅動表還是t1，并沒有改變執行順序。
需要在加上一個hint
SWAP_JOIN_INPUTS ：說明連接當中誰做內建表（驅動表）
NO_SWAP_JOIN_INPUTS ：說明連接中誰做探測表（被驅動表）
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")通過執行計劃可以看到，此時驅動表已經變成了t2，被驅動表變成了t1，同時可以看到id=2的操作，
從原來的HASH JOIN OUTER 變成了HASH JOIN RIGHT OUTER，這部分是等價的，
相當于t1左外連接t2改寫為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")此時hint為生效，還是原來的執行計劃。
4、當執行計劃是hash，t1是驅動表(主表)，t2是被驅動表，調整執行計劃為nl。
把hash調整為nl
驅動表：t1
被驅動表：t2
t2的id創建索引
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")執行計劃中已經從hash變為nl，并且t1是驅動表，t2是被驅動表
把hash調整為nl
驅動表：t2
被驅動表：t1
t1的id創建索引
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")通過執行計劃可以看到，驅動表還是t1，并沒有改變執行順序。
原因和走nl，不能調整驅動表和被驅動表的原理一致，只可以改變表的連接方式，但是不能改變表的訪問順序。
5、當外連接有OR關聯條件，進行等價改寫(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的全部數據以及滿足條件的T2記錄，還有不滿足條件的T2的結果null。
當使用了OR，則表示只要滿足其中的一個條件即可返回T1和T2的記錄。
假設T1和T2連接時是從第一行記錄開始： 當T1拿出第一行記錄的id和age的值傳給T2表，和T2表的第一行記錄進行匹配， 在這里有三種情況： 1、如果發現T1的id值和T2表的id的值相等，但是T1的age值和T2表的id的值不相等，那么返回T1的記錄和T2的記錄，第一行的記錄； 2、如果發現T1的age值和T2表的id的值相等，但是T1的id值和T2表的id的值不相等，那么也返回T1的記錄和T2的記錄，第一行的記錄； 3、如果發現T1的id值以及age值和T2表的id的值都相等，那么也返回T1的記錄和T2的記錄，第一行的記錄； 這三種情況的結果就是要么返回一條記錄，要么都不滿足的情況下T2返回null 當第一行記錄匹配完了，接下去該對T1的第二行記錄和T2的第二行記錄進行匹配，匹配的方法和情況還是和上述的方法一致。 直到把T1的所有記錄都匹配一遍，才最終的得到滿足條件的記錄和不滿足條件的T2的null。 因此在這種情況下，需要一行一行的去匹配數據，所以優化器選擇了使用nl，需要嵌套循環的匹配數據。
這時候的執行計劃肯定是有問題的：
1、被驅動表是全表掃描，連接列沒有索引，t1傳出一條數據，t2就需要全表掃描一次。
2、一般來說，走nl是小表在前，大表在后，但是在外連接中，走了nl，或者確定了主表，那么他就一定是驅動表，
這里的主表可以是一個表，也可以是一個過濾完的結果集，因此當主表的結果集很大的時候，驅動表就需要被驅動很多次，
做了大量的join操作，耗費很多的資源。
幾種情況：
t1是小表，t2是大表，但是t2列沒有索引，都是全表掃描；
t1是小表，t2是小表，但是t2列沒有索引，都是全表掃描；
t1是大表，t2是大表，但是t2列沒有索引，都是全表掃描；
t1是大表，t2是小表，但是t2列沒有索引，都是全表掃描；
以上的操作都是有問題，走的是nl，但是被驅動表都是全表掃描。
還有其他情況，t2表的連接列有索引
t1是小表，t2是大表，但是t2列有索引；
t1是小表，t2是小表，但是t2列有索引；
t1是大表，t2是大表，但是t2列有索引；
t1是大表，t2是小表，但是t2列有索引；
以上的操作相比較全表掃描而言性能有所提高，但是也是存在大量的join。
當t2的id列有索引時
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"))因為連接條件都是對t2的id進行關聯，在t2的連接條件上有索引時，會使用索引，但是會進行兩次索引掃描，然后回表，
然后把這個結果集作為一個視圖。
t1給一條記錄，則掃描一次視圖，這樣也是有問題的。
使用上述操作時存在以下問題：
1、訪問方式被固定，只能使用nl，不管被驅動表的連接列是否有索引
2、當驅動表很大，被驅動表很小，使用nl的效率很低，被驅動表需要訪問t1的行記錄數(結果集)
優化思路：
1、調整驅動表和被驅動表的順序
2、使用hash
1、調整驅動表和被驅動表的順序
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的外連接無法更改驅動表被驅動表。
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 ：說明連接當中誰做內建表（驅動表）
NO_SWAP_JOIN_INPUTS ：說明連接中誰做探測表（被驅動表）
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"))無法把執行計劃調整為hash。
最終思路：
需要進行等價改寫，使得這樣的查詢執行計劃不走nl，或者可以更改驅動表(不可能，前面提過，nl的外連接無法更改驅動表)。
因此只慮等價改寫，用來消除or的影響。
在進行等價改寫時，又分為兩種情況：
1、t2的id字段沒有重復值
2、t2的id字段有重復值
當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;
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")
當t2的id列有索引時
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")通過執行計劃可以看到，不走nl，都走了hash，并且通過Starts列可以看到，對每個表的訪問次數都是1，
達到了通過改寫SQL把nl調整為hash的效果。
最終的優化效果，邏輯讀由49降到了22。
當t2的id字段有重復值，進行等價改寫：
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"))
等價改寫(感謝劉老師指導)
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"))通過執行計劃可以看到，不走nl，都走了hash，并且通過Starts列可以看到，對每個表的訪問次數都是1，
達到了通過改寫SQL把nl調整為hash的效果。
最終的優化效果，邏輯讀由49降到了21。
“數據庫中外連接有OR關聯條件只能走NL優化的方法是什么”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
