本文介绍了“只有在数据库中存在中外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优化的方法是什么”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站,小编将为大家输出更多高质量的实用文章!
内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/68677.html