数据库中外连接有OR关联条件只能走NL优化的方法是什么

技术数据库中外连接有OR关联条件只能走NL优化的方法是什么本篇内容介绍了“数据库中外连接有OR关联条件只能走NL优化的方法是什么”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一

本文介绍了“只有在数据库中存在中外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

(0)

相关推荐

  • ajax翻译成什么意思(ajax用英文怎么读)

    技术ajax单词的意思是什么本篇内容主要讲解“ajax单词的意思是什么”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“ajax单词的意思是什么”吧!

    攻略 2021年12月17日
  • MongoDB Query的命令行分别是哪些

    技术MongoDB Query的命令行分别是哪些这期内容当中小编将会给大家带来有关MongoDB Query的命令行分别是哪些,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。Query.

    攻略 2021年11月3日
  • oracle数据库CPU过高问题分析

    技术oracle数据库CPU过高问题分析这篇文章主要讲解了“oracle数据库CPU过高问题分析”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“oracle数据库CPU过高

    攻略 2021年11月11日
  • 日志删除脚本怎么写

    技术日志删除脚本怎么写这篇文章主要介绍了日志删除脚本怎么写,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。#!/bin/bashfunction clear

    攻略 2021年11月9日
  • 怎么使用c语言中typedef关键字

    技术怎么使用c语言中typedef关键字本篇内容主要讲解“怎么使用c语言中typedef关键字”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“怎么使用c语言中typedef关键

    攻略 2021年10月28日
  • 梅子金黄杏子肥下一句,梅子金黄杏子肥的下一句是什么

    技术梅子金黄杏子肥下一句,梅子金黄杏子肥的下一句是什么梅子金黄杏子肥的下一句是麦花雪白菜花稀梅子金黄杏子肥下一句。原诗为: 四时田园杂兴·其二——范成大 梅子金黄杏子肥,麦花雪白菜花稀。日长篱落无人过,惟有蜻蜓蛱蝶飞。解

    生活 2021年10月26日