外連線有 OR 關聯條件只能走 NL優化
外連線有 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。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31536355/viewspace-2683241/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於外連線和where條件
- 連線條件是兩個欄位“or”關係的SQL優化SQL優化
- 資料庫外連線，自然連線，內連線，條件連線，等值連線關係及詳解資料庫
- Oracle 連線條件中帶有OR的測試Oracle
- 內聯，左外聯，右外聯，全連線，交叉連線 的區別
- PostgreSQL優化案例之 反連線與外連線等價改寫SQL優化
- 通過新增條件優化SQL優化SQL
- Java 條件表示式的優化Java優化
- 連線條件字元型別不一樣。字元型別
- MySQL 針對 like 條件的優化MySql優化
- mysql外連線有哪些型別MySql型別
- Oracle內連線、外連線、右外連線、全外連線小總結Oracle
- SQL優化--多表連線和走索引的關係SQL優化索引
- 11gr2全外連線優化執行計劃優化
- T-SQL學習中–內聯接，外連線，交叉連線SQL
- 【SQL】Oracle的內連線、左外連線、右外連線及全外連線SQLOracle
- Oracle左外連線、右外連線、完全外連線以及(+)號用法Oracle
- Oracle 左外連線、右外連線、全外連線小總結Oracle
- 內連線、外連線
- 有關連線池的疑惑。
- SQL的四種連線:內連線 左外連線 右外連線 全連線SQL
- MySQL表連線及其優化MySql優化
- 11gr2全外連線優化執行計劃（二）優化
- 11gr2全外連線優化執行計劃（三）優化
- 【機器學習之數學】03 有約束的非線性優化問題——拉格朗日乘子法、KKT條件、投影法機器學習優化
- mysql關聯查詢優化MySql優化
- 程式碼優化-多型代替IF條件判斷優化多型
- 優化 JS 條件語句的 5 個技巧優化JS
- ORACLE SQL過濾條件是IS NULL or !=的優化OracleSQLNull優化
- Mysql關於長連線短連線優劣比較MySql
- 【資料庫】查詢優化之子連線優化資料庫優化
- 深入理解SQL的四種連線-左外連線、右外連線、內連線、全連線SQL
- 支援向量機（非線性模型）——改寫優化目標函式和限制條件模型優化函式
- JavaScript（ES6）邏輯判斷條件優化JavaScript優化
- 蝦扯蛋之條件判斷的極致優化優化
- 一個NOT EXISTS含有OR條件子查詢的優化優化
- sql 內連線和外連線SQL
- 外連線與連線順序