数据库中外连接有OR关联条件只能走NL优化的方法是什么
本篇内容介绍了“数据库中外连接有OR关联条件只能走NL优化的方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
测试数据
droptablet1purge;droptablet2purge;createtablet1(idint,namevarchar2(10),ageint);insertintot1values(1,'a',1);insertintot1values(2,'b',2);insertintot1values(3,'c',5);insertintot1values(4,'d',1);insertintot1values(5,'e',3);insertintot1values(6,'f',6);createtablet2(idint,namevarchar2(10));insertintot2values(1,'a');insertintot2values(2,'b');insertintot2values(3,'c');insertintot2values(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：
SELECTT1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ONT1.ID=T2.IDORDERBY1;T1_IDT1_NAMET1_AGET2_IDT2_NAME--------------------------------------------------1a11a2b22b3c53c4d15e36f6执行计划：Planhashvalue:3645848104-----------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|-----------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|11|||||1|SORTORDERBY||1|6|6|00:00:00.01|11|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|6|6|00:00:00.01|11|||||3|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||4|TABLEACCESSBYINDEXROWID|T2|6|1|3|00:00:00.01|4|||||*5|INDEXRANGESCAN|IDX_ID_T2_01|6|1|3|00:00:00.01|3||||-----------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------5-access("T1"."ID"="T2"."ID")
通过执行计划可以看到，走了nl，并且t1是驱动表。
1、当执行计划是nl，t1是驱动表(主表)，t2是被驱动表，调整t2为驱动表，t1为被驱动表。
在内连接中，可以实现驱动表和被驱动表的调整，但是在外连接中不能调整驱动表的顺序
SELECT/*+leading(t2t1)use_nl(t1)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ONT1.ID=T2.IDORDERBY1;Planhashvalue:109855138--------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|--------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|11|||||1|SORTORDERBY||1|6|6|00:00:00.01|11|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|6|6|00:00:00.01|11|||||3|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||4|TABLEACCESSBYINDEXROWID|T2|6|1|3|00:00:00.01|4|||||*5|INDEXRANGESCAN|IDX_ID_T2|6|1|3|00:00:00.01|3||||--------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------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(t2t1)use_hash(t1)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ONT1.ID=T2.IDORDERBY1;Planhashvalue:109855138--------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|--------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|11|||||1|SORTORDERBY||1|6|6|00:00:00.01|11|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|6|6|00:00:00.01|11|||||3|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||4|TABLEACCESSBYINDEXROWID|T2|6|1|3|00:00:00.01|4|||||*5|INDEXRANGESCAN|IDX_ID_T2|6|1|3|00:00:00.01|3||||--------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------5-access("T1"."ID"="T2"."ID")
此时的hint未生效，走了最初的nl连接。
尝试使用其他hint
SWAP_JOIN_INPUTS：说明连接当中谁做内建表（驱动表）NO_SWAP_JOIN_INPUTS：说明连接中谁做探测表（被驱动表）SELECT/*+leading(t2t1)use_hash(t1)no_swap_join_inputs(t1)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ONT1.ID=T2.IDORDERBY1;Planhashvalue:109855138--------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|--------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|11|||||1|SORTORDERBY||1|6|6|00:00:00.01|11|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|6|6|00:00:00.01|11|||||3|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||4|TABLEACCESSBYINDEXROWID|T2|6|1|3|00:00:00.01|4|||||*5|INDEXRANGESCAN|IDX_ID_T2|6|1|3|00:00:00.01|3||||--------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------5-access("T1"."ID"="T2"."ID")
此时的hint未生效，走了最初的nl连接。
原因和走nl，不能调整驱动表和被驱动表的原理一致，只可以改变表的连接方式，但是不能改变表的访问顺序。
3、当执行计划是hash，t1是驱动表(主表)，t2是被驱动表，调整t2为驱动表，t1为被驱动表。
想办法调整表的访问顺序
使用hint：use_hash()
驱动表：t2
被驱动表：t1
SELECT/*+leading(t2t1)use_hash(t1)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ONT1.ID=T2.IDORDERBY1;Planhashvalue:2391546071-----------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|-----------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|14|||||1|SORTORDERBY||1|6|6|00:00:00.01|14|2048|2048|2048(0)||*2|HASHJOINOUTER||1|6|6|00:00:00.01|14|1753K|1753K|920K(0)||3|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||4|TABLEACCESSFULL|T2|1|3|3|00:00:00.01|7||||-----------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("T1"."ID"="T2"."ID")
通过执行计划可以看到，驱动表还是t1，并没有改变执行顺序。
需要在加上一个hintSWAP_JOIN_INPUTS：说明连接当中谁做内建表（驱动表）NO_SWAP_JOIN_INPUTS：说明连接中谁做探测表（被驱动表）SELECT/*+leading(t2t1)use_hash(t1)swap_join_inputs(t2)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ONT1.ID=T2.IDORDERBY1;Planhashvalue:2146067096--------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|--------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|14|||||1|SORTORDERBY||1|6|6|00:00:00.01|14|2048|2048|2048(0)||*2|HASHJOINRIGHTOUTER||1|6|6|00:00:00.01|14|2061K|2061K|872K(0)||3|TABLEACCESSFULL|T2|1|3|3|00:00:00.01|7|||||4|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7||||--------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("T1"."ID"="T2"."ID")
通过执行计划可以看到，此时驱动表已经变成了t2，被驱动表变成了t1，同时可以看到id=2的操作，
从原来的HASH JOIN OUTER 变成了HASH JOIN RIGHT OUTER，这部分是等价的，
相当于t1左外连接t2改写为t2右外连接t1。
SELECT/*+leading(t2t1)use_hash(t1)no_swap_join_inputs(t1)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ONT1.ID=T2.IDORDERBY1;Planhashvalue:2391546071-----------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|-----------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|14|||||1|SORTORDERBY||1|6|6|00:00:00.01|14|2048|2048|2048(0)||*2|HASHJOINOUTER||1|6|6|00:00:00.01|14|1753K|1753K|886K(0)||3|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||4|TABLEACCESSFULL|T2|1|3|3|00:00:00.01|7||||-----------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("T1"."ID"="T2"."ID")
此时hint为生效，还是原来的执行计划。
4、当执行计划是hash，t1是驱动表(主表)，t2是被驱动表，调整执行计划为nl。
把hash调整为nl
驱动表：t1
被驱动表：t2
t2的id创建索引createindexidx_id_t2ont2(id);SELECTT1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ONT1.ID=T2.IDORDERBY1;Planhashvalue:109855138--------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|--------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|11|||||1|SORTORDERBY||1|6|6|00:00:00.01|11|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|6|6|00:00:00.01|11|||||3|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||4|TABLEACCESSBYINDEXROWID|T2|6|1|3|00:00:00.01|4|||||*5|INDEXRANGESCAN|IDX_ID_T2|6|1|3|00:00:00.01|3||||--------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------5-access("T1"."ID"="T2"."ID")
执行计划中已经从hash变为nl，并且t1是驱动表，t2是被驱动表
把hash调整为nl
驱动表：t2
被驱动表：t1
t1的id创建索引createindexidx_id_t1ont1(id);SELECT/*+leading(t2t1)use_nl(t1)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ONT1.ID=T2.IDORDERBY1;Planhashvalue:109855138--------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|--------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|11|||||1|SORTORDERBY||1|6|6|00:00:00.01|11|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|6|6|00:00:00.01|11|||||3|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||4|TABLEACCESSBYINDEXROWID|T2|6|1|3|00:00:00.01|4|||||*5|INDEXRANGESCAN|IDX_ID_T2|6|1|3|00:00:00.01|3||||--------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------5-access("T1"."ID"="T2"."ID")
通过执行计划可以看到，驱动表还是t1，并没有改变执行顺序。
原因和走nl，不能调整驱动表和被驱动表的原理一致，只可以改变表的连接方式，但是不能改变表的访问顺序。
5、当外连接有OR关联条件，进行等价改写(2)
SELECTT1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ON(T1.ID=T2.IDORT1.AGE=T2.ID)ORDERBY1;T1_IDT1_NAMET1_AGET2_IDT2_NAME--------------------------------------------------1a11a2b22b3c53c4d11a5e33c6f66rowsselected.Planhashvalue:3004654521------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|49|||||1|SORTORDERBY||1|6|6|00:00:00.01|49|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|6|6|00:00:00.01|49|||||3|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||4|VIEW||6|1|5|00:00:00.01|42|||||*5|TABLEACCESSFULL|T2|6|1|5|00:00:00.01|42||||------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------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列有索引时
createindexidx_id_t2ont2(id);SELECTT1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ON(T1.ID=T2.IDORT1.AGE=T2.ID)ORDERBY1;Planhashvalue:2234182087----------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|----------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|24|||||1|SORTORDERBY||1|12|6|00:00:00.01|24|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|12|6|00:00:00.01|24|||||3|TABLEACCESSFULL|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|TABLEACCESSBYINDEXROWID|T2|6|1|4|00:00:00.01|10|||||*7|INDEXRANGESCAN|IDX_ID_T2|6|2|4|00:00:00.01|6|||||8|TABLEACCESSBYINDEXROWID|T2|6|1|1|00:00:00.01|7|||||*9|INDEXRANGESCAN|IDX_ID_T2|6|2|1|00:00:00.01|6||||----------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------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(t2t1)use_nl(t1)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ON(T1.ID=T2.IDORT1.AGE=T2.ID)ORDERBY1;Planhashvalue:2234182087----------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|----------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|24|||||1|SORTORDERBY||1|12|6|00:00:00.01|24|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|12|6|00:00:00.01|24|||||3|TABLEACCESSFULL|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|TABLEACCESSBYINDEXROWID|T2|6|1|4|00:00:00.01|10|||||*7|INDEXRANGESCAN|IDX_ID_T2|6|2|4|00:00:00.01|6|||||8|TABLEACCESSBYINDEXROWID|T2|6|1|1|00:00:00.01|7|||||*9|INDEXRANGESCAN|IDX_ID_T2|6|2|1|00:00:00.01|6||||----------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------7-access("T1"."AGE"="T2"."ID")9-access("T1"."ID"="T2"."ID")filter(LNNVL("T1"."AGE"="T2"."ID"))
原因：nl的外连接无法更改驱动表被驱动表。
2、使用hash
SELECT/*+leading(t1t2)use_hash(t2)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ON(T1.ID=T2.IDORT1.AGE=T2.ID)ORDERBY1;Planhashvalue:2234182087----------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|----------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|24|||||1|SORTORDERBY||1|12|6|00:00:00.01|24|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|12|6|00:00:00.01|24|||||3|TABLEACCESSFULL|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|TABLEACCESSBYINDEXROWID|T2|6|1|4|00:00:00.01|10|||||*7|INDEXRANGESCAN|IDX_ID_T2|6|2|4|00:00:00.01|6|||||8|TABLEACCESSBYINDEXROWID|T2|6|1|1|00:00:00.01|7|||||*9|INDEXRANGESCAN|IDX_ID_T2|6|2|1|00:00:00.01|6||||----------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------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(t1t2)use_hash(t2)swap_join_inputs(t1)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ON(T1.ID=T2.IDORT1.AGE=T2.ID)ORDERBY1;Planhashvalue:2234182087----------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|----------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|24|||||1|SORTORDERBY||1|12|6|00:00:00.01|24|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|12|6|00:00:00.01|24|||||3|TABLEACCESSFULL|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|TABLEACCESSBYINDEXROWID|T2|6|1|4|00:00:00.01|10|||||*7|INDEXRANGESCAN|IDX_ID_T2|6|2|4|00:00:00.01|6|||||8|TABLEACCESSBYINDEXROWID|T2|6|1|1|00:00:00.01|7|||||*9|INDEXRANGESCAN|IDX_ID_T2|6|2|1|00:00:00.01|6||||----------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------7-access("T1"."AGE"="T2"."ID")9-access("T1"."ID"="T2"."ID")filter(LNNVL("T1"."AGE"="T2"."ID"))SELECT/*+leading(t1t2)use_hash(t2)no_swap_join_inputs(t2)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ON(T1.ID=T2.IDORT1.AGE=T2.ID)ORDERBY1;Planhashvalue:2234182087----------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|----------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|24|||||1|SORTORDERBY||1|12|6|00:00:00.01|24|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|12|6|00:00:00.01|24|||||3|TABLEACCESSFULL|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|TABLEACCESSBYINDEXROWID|T2|6|1|4|00:00:00.01|10|||||*7|INDEXRANGESCAN|IDX_ID_T2|6|2|4|00:00:00.01|6|||||8|TABLEACCESSBYINDEXROWID|T2|6|1|1|00:00:00.01|7|||||*9|INDEXRANGESCAN|IDX_ID_T2|6|2|1|00:00:00.01|6||||----------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------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(SELECTT.*,ROW_NUMBER()OVER(PARTITIONBYT.T1_RIDORDERBYT.T2_ID)RNFROM(SELECTT1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAME,T1.ROWIDT1_RIDFROMT1LEFTJOINT2ONT1.ID=T2.IDUNIONALLSELECTT1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAME,T1.ROWIDT1_RIDFROMT1LEFTJOINT2ONT1.AGE=T2.ID)T)WHERERN=1orderby1;T1_IDT1_NAMET1_AGET2_IDT2_NAMET1_RIDRN------------------------------------------------------------------------------1a11aAAAVuJAAEAAAByUAAA12b22bAAAVuJAAEAAAByUAAB13c53cAAAVuJAAEAAAByUAAC14d11aAAAVuJAAEAAAByUAAD15e33cAAAVuJAAEAAAByUAAE16f6AAAVuJAAEAAAByUAAF16rowsselected.Planhashvalue:3180408145-----------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|-----------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|28|||||1|SORTORDERBY||1|12|6|00:00:00.01|28|2048|2048|2048(0)||*2|VIEW||1|12|6|00:00:00.01|28|||||*3|WINDOWSORTPUSHEDRANK||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|HASHJOINOUTER||1|6|6|00:00:00.01|14|1321K|1321K|939K(0)||7|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||8|TABLEACCESSFULL|T2|1|3|3|00:00:00.01|7|||||*9|HASHJOINOUTER||1|6|6|00:00:00.01|14|1321K|1321K|939K(0)||10|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||11|TABLEACCESSFULL|T2|1|3|3|00:00:00.01|7||||-----------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-filter("RN"=1)3-filter(ROW_NUMBER()OVER(PARTITIONBY"T"."T1_RID"ORDERBY"T"."T2_ID")<=1)6-access("T1"."ID"="T2"."ID")9-access("T1"."AGE"="T2"."ID")当t2的id列有索引时createindexidx_id_t2ont2(id);SELECT*FROM(SELECTT.*,ROW_NUMBER()OVER(PARTITIONBYT.T1_RIDORDERBYT.T2_ID)RNFROM(SELECTT1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAME,T1.ROWIDT1_RIDFROMT1LEFTJOINT2ONT1.ID=T2.IDUNIONALLSELECTT1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAME,T1.ROWIDT1_RIDFROMT1LEFTJOINT2ONT1.AGE=T2.ID)T)WHERERN=1orderby1;Planhashvalue:1354803237------------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|------------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|25|||||1|SORTORDERBY||1|12|6|00:00:00.01|25|2048|2048|2048(0)||*2|VIEW||1|12|6|00:00:00.01|25|||||*3|WINDOWSORTPUSHEDRANK||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|NESTEDLOOPSOUTER||1|6|6|00:00:00.01|11|||||7|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||8|TABLEACCESSBYINDEXROWID|T2|6|1|3|00:00:00.01|4|||||*9|INDEXRANGESCAN|IDX_ID_T2|6|1|3|00:00:00.01|3|||||*10|HASHJOINOUTER||1|6|6|00:00:00.01|14|1321K|1321K|897K(0)||11|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||12|TABLEACCESSFULL|T2|1|3|3|00:00:00.01|7||||------------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-filter("RN"=1)3-filter(ROW_NUMBER()OVER(PARTITIONBY"T"."T1_RID"ORDERBY"T"."T2_ID")<=1)9-access("T1"."ID"="T2"."ID")10-access("T1"."AGE"="T2"."ID")上面的查询使用了索引，但是下面的查询并未用到索引，可以使用hint指定使用索引SELECT*FROM(SELECTT.*,ROW_NUMBER()OVER(PARTITIONBYT.T1_RIDORDERBYT.T2_ID)RNFROM(SELECTT1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAME,T1.ROWIDT1_RIDFROMT1LEFTJOINT2ONT1.ID=T2.IDUNIONALLSELECT/*+leading(t1t2)use_nl(t2)*/T1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAME,T1.ROWIDT1_RIDFROMT1LEFTJOINT2ONT1.AGE=T2.ID)T)WHERERN=1orderby1;Planhashvalue:4092066186------------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|------------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||6|00:00:00.01|22|||||1|SORTORDERBY||1|12|6|00:00:00.01|22|2048|2048|2048(0)||*2|VIEW||1|12|6|00:00:00.01|22|||||*3|WINDOWSORTPUSHEDRANK||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|NESTEDLOOPSOUTER||1|6|6|00:00:00.01|11|||||7|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||8|TABLEACCESSBYINDEXROWID|T2|6|1|3|00:00:00.01|4|||||*9|INDEXRANGESCAN|IDX_ID_T2|6|1|3|00:00:00.01|3|||||10|NESTEDLOOPSOUTER||1|6|6|00:00:00.01|11|||||11|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||12|TABLEACCESSBYINDEXROWID|T2|6|1|4|00:00:00.01|4|||||*13|INDEXRANGESCAN|IDX_ID_T2|6|1|4|00:00:00.01|3||||------------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-filter("RN"=1)3-filter(ROW_NUMBER()OVER(PARTITIONBY"T"."T1_RID"ORDERBY"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*fromt1;IDNAMEAGE------------------------------1a12b23c54d15e36f66rowsselected.Elapsed:00:00:00.01SQL>select*fromt2;IDNAME--------------------1a2b3c1ySELECTT1.IDT1_ID,T1.NAMET1_NAME,T1.AGET1_AGE,T2.IDT2_ID,T2.NAMET2_NAMEFROMT1LEFTJOINT2ON(T1.ID=T2.IDORT1.AGE=T2.ID)ORDERBY1;T1_IDT1_NAMET1_AGET2_IDT2_NAME--------------------------------------------------1a11a1a11y2b22b3c53c4d11a4d11y5e33c6f68rowsselected.Planhashvalue:3004654521------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||8|00:00:00.01|49|||||1|SORTORDERBY||1|6|8|00:00:00.01|49|2048|2048|2048(0)||2|NESTEDLOOPSOUTER||1|6|8|00:00:00.01|49|||||3|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||4|VIEW||6|1|7|00:00:00.01|42|||||*5|TABLEACCESSFULL|T2|6|1|7|00:00:00.01|42||||------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------5-filter(("T1"."ID"="T2"."ID"OR"T1"."AGE"="T2"."ID"))等价改写(感谢刘老师指导)WITHTMP_AAS(SELECTID,NAME,AGE,0ASFLAGFROMT1UNIONALLSELECTAGE,NAME,ID,NULLFROMT1WHERELNNVL(ID=AGE)),TMP_BAS(SELECTA.ID,A.NAME,A.AGE,A.FLAG,B.IDASBID,B.NAMEASBNAMEFROMTMP_AALEFTJOINT2BONA.ID=B.ID),TMP_CAS(SELECTNVL2(FLAG,ID,AGE)ASID,NAME,NVL2(FLAG,AGE,ID)ASAGE,BID,BNAME,FLAG,DENSE_RANK()OVER(PARTITIONBYNVL2(FLAG,ID,AGE),NAME,NVL2(FLAG,AGE,ID)ORDERBYNVL2(BID,1,NULL)NULLSLAST)ASDRNFROMTMP_B)SELECTID,NAME,AGE,BID,BNAME--,drn,flagFROMTMP_CWHEREDRN=1AND(FLAGISNOTNULLORBIDISNOTNULL)ORDERBY1,2,3,4,5;Planhashvalue:1011965060-----------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|-----------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||8|00:00:00.01|21|||||1|SORTORDERBY||1|12|8|00:00:00.01|21|2048|2048|2048(0)||*2|VIEW||1|12|8|00:00:00.01|21|||||*3|WINDOWSORTPUSHEDRANK||1|12|11|00:00:00.01|21|2048|2048|2048(0)||*4|HASHJOINOUTER||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|TABLEACCESSFULL|T1|1|6|6|00:00:00.01|7|||||*8|TABLEACCESSFULL|T1|1|3|3|00:00:00.01|7|||||9|TABLEACCESSFULL|T2|1|4|4|00:00:00.01|7||||-----------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-filter(("DRN"=1AND("FLAG"ISNOTNULLOR"BID"ISNOTNULL)))3-filter(DENSE_RANK()OVER(PARTITIONBYNVL2("A"."FLAG","A"."ID","A"."AGE"),"A"."NAME",NVL2("A"."FLAG","A"."AGE","A"."ID")ORDERBYNVL2("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优化的方法是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
