09月22日, 2014 132次
正文引见了“惟有在数据库中生存中外OR关系前提时，NL优化的本领是什么”的常识。很多人在本质案例的操纵中会遇到如许的艰巨。让边肖率领你进修怎样处置那些情景。蓄意大师刻意观赏，学点货色！
droptablet1purge
droppet table 2 pure；
createtablet1(idint，namevarchar2(10)，ageint)；
insertintot1values(1， a ，1)；
插入t1values(2， b ，2)；
插入t1values(3， c ，5)；
插入t1values(4， d ，1)；
插入t1值(5， e ，3)；
插入t1值(6， f ，6)；
createtablet2(idint，namevarchar 2(10))；
插入t2values(1， a )；
插入t2values(2， b )；
插入t2values(3， c )；
插入t2values(1， y )；与“或”关系的关系前提只能为“1”。即使启动表的截止集很大，会爆发洪量的关系，会形成本能题目，须要优化。
两个表在外部贯穿时有几种情景：
1.在外部贯穿功夫，运用nl。此时主桌恒定为驾驶桌，没辙经过提醒安排驾驶桌。
2.举行外部贯穿时，运用hash，不妨经过提醒安排启动表和从动表。
按照外部贯穿，做以次试验：
1.当实行安置为n1，t1为启动表(主表)，t2为从动表时，安排t2为启动表，t1为从动表。
2.当实行安置为nl，t1为启动表(主表)，t2为从动表时，将实行安置安排为hash。
3.当实行安置为hash时，t1为启动表(主表)，t2为从动表，安排t2为启动表，t1为从动表。
4.当实行安置为hash时，t1为启动表(主表)，t2为从动表，实行安置安排为nl。
5.当或关系前提(T1。ID=T2。辨别号或T1。年纪=T2。ID)是外部贯穿的，则实行等效重写。
实行安置是nl。
有以次SQL:
采用1。IDT1_ID
，T1。称呼1 _称呼
，T1。年纪1 _年纪
，T2。IDT2_ID
，T2。称呼2 _称呼
FROMT1
LEFTJOINT2
ONT1。ID=T2。身份
ORDERBY1
T1_IDT1_NAMET1_AGET2_IDT2_NAME
1a1nbsp
;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优化的本领是什么”的实质就引见到这边了，感动大师的观赏。即使想领会更多行业关系的常识不妨关心网站，小编将为大师输入更多高品质的适用作品！