数据库中外连接有OR关联条件只能走NL优化的方法是什么
发表于：2025-02-02 作者：千家信息网编辑
千家信息网最后更新 2025年02月02日，本篇内容介绍了"数据库中外连接有OR关联条件只能走NL优化的方法是什么"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读
千家信息网最后更新 2025年02月02日数据库中外连接有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_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER 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_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER 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_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER 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_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER 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_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER 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，并没有改变执行顺序。
需要在加上一个hintSWAP_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_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER 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_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER 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_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER 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_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER 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_NAMEFROM T1LEFT JOIN T2ON (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 66 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_NAMEFROM T1LEFT JOIN T2ON (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_NAMEFROM T1LEFT JOIN T2ON (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_NAMEFROM T1LEFT JOIN T2ON (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_NAMEFROM T1LEFT JOIN T2ON (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_NAMEFROM T1LEFT JOIN T2ON (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 = 1order 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 16 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 = 1order 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 = 1order 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 66 rows selected.Elapsed: 00:00:00.01SQL> select * from t2; ID NAME---------- ---------- 1 a 2 b 3 c 1 ySELECT T1.ID T1_ID ,T1.NAME T1_NAME ,T1.AGE T1_AGE ,T2.ID T2_ID ,T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON (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 68 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,flagFROM TMP_CWHERE 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优化的方法是什么"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！
驱动
调整
索引
条件
情况
数据
关联
一行
等价
顺序
结果
还是
问题
方法
原因
字段
效果
一致
方式
循环
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
手机安卓聊天软件开发
南阳智强软件开发公司
远程管理控制服务器我爱破解
手机无法和服务器建立安全连接
阿里云国际服务器登录
网络安全的纲领性文件
网络安全的三个原则
南京软件开发公司韩大斌
易用的零信任网络安全
网络安全健康发展
多举措应对网络安全实战演练
中国志愿服务器
手机服务器设置密码错误
甘肃肃南网络安全宣传
下拉框获取数据库的值
哪里学游戏软件开发
我的世界历练者服务器
天津大学国家网络安全宣传周
网络技术来抗疫
租用阿里云服务器有缺点
湖北网络安全审计硬件设备
网络安全的小报怎么做
廊坊企联网络技术服务公司
从事多年网络安全工作
艾瑞软件开发有限公司
我的世界柏林防空塔服务器
西门子上位机软件开发
因为链接不到服务器
苹果官网服务器是不是星期日维护
如何对付网络安全挑战
