ITPub博客

首页 > 数据库 > Oracle > 外连接有 OR 关联条件只能走 NL优化

外连接有 OR 关联条件只能走 NL优化

原创 Oracle 作者:yhluffy 时间:2020-03-29 22:02:37 1 删除 编辑

外连接有 OR 关联条件只能走 NL优化

测试数据

drop table t1 purge;
drop table t2 purge;
create table t1 (id int,name varchar2(10),age int);
insert into t1 values(1,'a',1);
insert into t1 values(2,'b',2);
insert into t1 values(3,'c',5);
insert into t1 values(4,'d',1);
insert into t1 values(5,'e',3);
insert into t1 values(6,'f',6);
create table t2 (id int,name varchar2(10));
insert into t2 values(1,'a');
insert into t2 values(2,'b');
insert into t2 values(3,'c');
insert into t2 values(1,'y');

外连接有OR关联条件只能走NL,若驱动表的结果集较大,则会产生大量的关联,会产生性能问题,需要进行优化。

在两个表做外连接时存在几种情况:

1、在进行外连接时,使用nl,此时主表被固定成驱动表,无法通过hint进行驱动表的调整

2、在进行外连接时,使用hash,可以通过hint调整驱动表和被驱动表


针对外连接的情况做以下的实验:

1、当执行计划是nl,t1是驱动表(主表),t2是被驱动表,调整t2为驱动表,t1为被驱动表。

2、当执行计划是nl,t1是驱动表(主表),t2是被驱动表,调整执行计划为hash。

3、当执行计划是hash,t1是驱动表(主表),t2是被驱动表,调整t2为驱动表,t1为被驱动表。

4、当执行计划是hash,t1是驱动表(主表),t2是被驱动表,调整执行计划为nl。

5、当外连接有OR关联条件(T1.ID = T2.ID OR T1.AGE = T2.ID),进行等价改写


执行计划是nl的情况

有如下SQL:

SELECT T1.ID   T1_ID
      ,T1.NAME T1_NAME
      ,T1.AGE  T1_AGE
      ,T2.ID   T2_ID
      ,T2.NAME T2_NAME
FROM   T1
LEFT   JOIN T2
ON     T1.ID = 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
         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。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31536355/viewspace-2683241/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 行迁移和行链接
全部评论

注册时间:2018-05-14

  • 博文量
    35
  • 访问量
    22161