SQL优化案例 - 烂用外连接导致无法谓词推入 - 《SQL优化入门》

2015 年，一位甲骨文公司的朋友请求协助优化。有个 SQL 单次执行需要 26.57 秒，一共要执行 226 次，如图 9-12 所示。

烂用外连接导致无法谓词推入 - 图1

图 9-12

SQL 代码如下。

SELECT view_xj_ct.ybjshj  FROM view_xj_ct 
WHERE view_xj_ct.ct_code = :1 AND view_xj_ct.pk_corp = :2

view_xj_ct 是一个视图，视图定义如下。

CREATE OR REPLACE FORCE VIEW "JXNC"."VIEW_XJ_CT" ("CT_CODE", "PK_CT_MANAGE", "YBJSHJ", "FKHJ", "KPJE", "JE", "PK_CORP") AS 
  select a."CT_CODE",
       a."PK_CT_MANAGE",
       a."YBJSHJ",
       a."FKHJ",
       b.kpje,
       (case
         when b.kpje >= a.ybjshj then
          b.kpje
         else
          a.ybjshj
       end) je,
       pk_corp
  from (select cth.ct_code,
               cth.pk_ct_manage,
               sum(ctb.oritaxsummny) ybjshj,
               sum(ctv.ljfk) fkhj,
               ctb.pk_corp
          from ct_manage_b ctb
          left join ct_manage cth
            on ctb.pk_ct_manage = cth.pk_ct_manage
          left join view_xj_ct_fukuan ctv
            on ctv.pk_ct_manage_b = ctb.pk_ct_manage_b
           and ctv.pk_ct_manage = cth.pk_ct_manage
         where activeflag = 0
           and cth.dr = 0
           and ctb.dr = 0
         group by cth.ct_code, cth.pk_ct_manage, ctb.pk_corp) a
  left join (select cth.pk_ct_manage, sum(fp.noriginalsummny) kpje
               from po_invoice_b fp
               left join po_order_b dd
                 on fp.csourcebillrowid = dd.corder_bid
               left join ct_manage_b ct
                 on ct.pk_ct_manage_b = dd.csourcerowid
               left join ct_manage cth
                 on ct.pk_ct_manage = cth.pk_ct_manage
              where fp.dr = 0
                and dd.cupsourcebilltype = 'Z2'
              group by cth.pk_ct_manage) b
    on b.pk_ct_manage = a.pk_ct_manage;

代码中：表 ct_manage_b 有数据 266 274（26 万条记录），表 ct_manage 有数据 88 563（8.8 万条记录），表 po_invoice_b 有数据 294 467（29 万条记录），表 po_order_b 有数据 143122（14 万条记录）。

上面视图 view_xj_ct 中又内嵌一个视图 view_xj_ct_fukuan，视图代码如下。

CREATE OR REPLACE FORCE VIEW "JXNC"."VIEW_XJ_CT_FUKUAN" ("DDHH", "PK_CORP", "PK_CT_MANAGE_B", "PK_CT_MANAGE", "LJFK", "CT_CODE") AS 
  select ddhh,
       a.pk_corp,
       a.pk_ct_manage_b,
       ctb.pk_ct_manage,
       sum(a.ljfk) ljfk,
       cth.ct_code
  from (select a.ddhh,
                a.dwbm pk_corp,
                a.zyx5 pk_ct_manage_b,
                a.jfybje ljfk
          from arap_djfb a
          left join arap_djzb b on a.vouchid = b.vouchid
         where a.dr = 0
           and b.dr = 0
           and a.djlxbm = 'D3'
           and a.jsfsbm in ('Z2', 'Z5','D1')
           and b.djzt not in ('-99', '1')) a
  left join ct_manage_b ctb on ctb.pk_ct_manage_b = a.pk_ct_manage_b
  left join ct_manage cth on cth.pk_ct_manage = ctb.pk_ct_manage
 group by ddhh, a.pk_corp, a.pk_ct_manage_b, ctb.pk_ct_manage, cth.ct_code
 order by a.pk_ct_manage_b;

其中：表 arap_djfb 有数据 1 175 707（117 万条记录），表 arap_djzb 有数据 149 157（15 万条记录），表 ct_manage_b 有数据 266 274（26 万条记录），表 ct_manage 有数据 88 563（8.8 万条记录）。

SQL 语句的执行计划如下。

explain plan for SELECT view_xj_ct.ybjshj  FROM view_xj_ct 
  2  WHERE view_xj_ct.ct_code = :1 AND view_xj_ct.pk_corp = :2;

Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3563589558
-------------------------------------------------------------------------------------
| Id|Operation                            |Name              |Rows |Bytes|TempSpc|Cost(%CPU)|Time    |
-------------------------------------------------------------------------------------
|  0|SELECT STATEMENT                     |                  |    1|   57|       |49994  (1)|00:10:00|
|* 1| HASH JOIN OUTER                     |                  |    1|   57|       |49994  (1)|00:10:00|
|  2|  VIEW                               |                  |    1|   35|       |32190  (1)|00:06:27|
|  3|   HASH GROUP BY                     |                  |    1|   74|       |32190  (1)|00:06:27|
|* 4|    HASH JOIN OUTER                  |                  |    1|   74|       |32189  (1)|00:06:27|
|  5|     VIEW                            |                  |    1|   35|       |    2  (0)|00:00:01|
|  6|      NESTED LOOPS                   |                  |     |     |       |          |        |
|  7|       NESTED LOOPS                  |                  |    1|   95|       |    2  (0)|00:00:01|
|* 8|        TABLE ACCESS BY INDEX ROWID  |CT_MANAGE         |    1|   40|       |    1  (0)|00:00:01|
|* 9|         INDEX RANGE SCAN            |I_CT_M_1          |    2|     |       |    1  (0)|00:00:01|
|*10|        INDEX RANGE SCAN             |I_CT_M_B_1        |    3|     |       |    1  (0)|00:00:01|
|*11|       TABLE ACCESS BY INDEX ROWID   |CT_MANAGE_B       |    1|   55|       |    1  (0)|00:00:01|
| 12|     VIEW                            |VIEW_XJ_CT_FUKUAN |39191|1492K|       |32186  (1)|00:06:27|
| 13|      HASH GROUP BY                  |                  |39191|6468K|  6976K|32186  (1)|00:06:27|
|*14|       HASH JOIN RIGHT OUTER         |                  |39191|6468K|  3976K|30726  (1)|00:06:09|
| 15|        TABLE ACCESS FULL            |CT_MANAGE         |88505|2938K|       | 1621  (2)|00:00:20|
|*16|        HASH JOIN OUTER              |                  |39191|5166K|  4024K|28636  (1)|00:05:44|
|*17|         HASH JOIN                   |                  |39191|3559K|  2952K|23574  (1)|00:04:43|
| 18|          INLIST ITERATOR            |                  |     |     |       |          |        |
|*19|          TABLE ACCESS BY INDEX ROWID|ARAP_DJFB         |39191|2487K|       |20692  (1)|00:04:09|
|*20|            INDEX RANGE SCAN         |I_ARAP_DJFB_JSZC02| 337K|     |       |  251  (2)|00:00:04|
|*21|          TABLE ACCESS FULL          |ARAP_DJZB         | 127K|3476K|       | 2494  (2)|00:00:30|
| 22|         TABLE ACCESS FULL           |CT_MANAGE_B       | 266K|  10M|       | 4179  (2)|00:00:51|
| 23|  VIEW                               |                  |88480|1900K|       |17802  (1)|00:03:34|
| 24|   HASH GROUP BY                     |                  |88480|  10M|    16M|17802  (1)|00:03:34|
|*25|    HASH JOIN                        |                  | 120K|  14M|  5024K|14906  (1)|00:02:59|
|*26|     TABLE ACCESS FULL               |PO_INVOICE_B      | 138K|3389K|       | 5263  (1)|00:01:04|
|*27|     HASH JOIN RIGHT OUTER           |                  |98165|   9M|  2856K| 8850  (2)|00:01:47|
| 28|      INDEX FAST FULL SCAN           |PK_CT_MANAGE      |88505|1815K|       |  107  (2)|00:00:02|
|*29|      HASH JOIN OUTER                |                  |98165|8052K|  5184K| 8154  (2)|00:01:38|
|*30|       TABLE ACCESS FULL             |PO_ORDER_B        |98165|4026K|       | 3035  (1)|00:00:37|
| 31|       TABLE ACCESS FULL             |CT_MANAGE_B       | 266K|  10M|       | 4179  (2)|00:00:51|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."PK_CT_MANAGE"(+)="A"."PK_CT_MANAGE")
   4 - access("CTV"."PK_CT_MANAGE"(+)="CTH"."PK_CT_MANAGE" AND
              "CTV"."PK_CT_MANAGE_B"(+)="CTB"."PK_CT_MANAGE_B")
   8 - filter("CTH"."DR"=0 AND "CTH"."ACTIVEFLAG"=0)
   9 - access("CTH"."CT_CODE"=:1)
  10 - access("CTB"."PK_CT_MANAGE"="CTH"."PK_CT_MANAGE")
  11 - filter("CTB"."PK_CORP"=:2 AND "CTB"."DR"=0)
  14 - access("CTH"."PK_CT_MANAGE"(+)="CTB"."PK_CT_MANAGE")
  16 - access("CTB"."PK_CT_MANAGE_B"(+)="A"."ZYX5")
  17 - access("A"."VOUCHID"="B"."VOUCHID")
  19 - filter("A"."DJLXBM"='D3' AND "A"."DR"=0)
  20 - access("A"."JSFSBM"='D1' OR "A"."JSFSBM"='Z2' OR "A"."JSFSBM"='Z5')
  21 - filter("B"."DR"=0 AND "B"."DJZT"<>1 AND "B"."DJZT"<>(-99))
  25 - access("FP"."CSOURCEBILLROWID"="DD"."CORDER_BID")
  26 - filter("FP"."CSOURCEBILLROWID" IS NOT NULL AND "FP"."DR"=0)
  27 - access("CT"."PK_CT_MANAGE"="CTH"."PK_CT_MANAGE"(+))
  29 - access("CT"."PK_CT_MANAGE_B"(+)="DD"."CSOURCEROWID")
  30 - filter("DD"."CUPSOURCEBILLTYPE"='Z2')
60 rows selected.

对于上述的执行计划，甲骨文公司的朋友创建了一个 index。

create index idx_jszc1026 on ARAP_djfb(jsfsbm,djlxbm,dr);

之前大约 26 秒出结果，创建新 index 后速度是 2.6 秒出结果，新建索引后的执行计划如下。

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 2820245905
-------------------------------------------------------------------------------------
| Id|Operation                            |Name             |Rows |Bytes|TempSpc|Cost(%CPU)|Time    |
-------------------------------------------------------------------------------------
|  0|SELECT STATEMENT                     |                 |    1|   57|       |32043  (1)|00:06:25|
|* 1| HASH JOIN OUTER                     |                 |    1|   57|       |32043  (1)|00:06:25|
|  2|  VIEW                               |                 |    1|   35|       |14239  (2)|00:02:51|
|  3|   HASH GROUP BY                     |                 |    1|   74|       |14239  (2)|00:02:51|
|* 4|    HASH JOIN OUTER                  |                 |    1|   74|       |14238  (2)|00:02:51|
|  5|     VIEW                            |                 |    1|   35|       |    2  (0)|00:00:01|
|  6|      NESTED LOOPS                   |                 |     |     |       |          |        |
|  7|       NESTED LOOPS                  |                 |    1|   95|       |    2  (0)|00:00:01|
|* 8|        TABLE ACCESS BY INDEX ROWID  |CT_MANAGE        |    1|   40|       |    1  (0)|00:00:01|
|* 9|         INDEX RANGE SCAN            |I_CT_M_1         |    2|     |       |    1  (0)|00:00:01|
|*10|        INDEX RANGE SCAN             |I_CT_M_B_1       |    3|     |       |    1  (0)|00:00:01|
|*11|       TABLE ACCESS BY INDEX ROWID   |CT_MANAGE_B      |    1|   55|       |    1  (0)|00:00:01|
| 12|     VIEW                            |VIEW_XJ_CT_FUKUAN|39191|1492K|       |14234  (2)|00:02:51|
| 13|      HASH GROUP BY                  |                 |39191|6468K|  6976K|14234  (2)|00:02:51|
|*14|       HASH JOIN RIGHT OUTER         |                 |39191|6468K|  3976K|12775  (2)|00:02:34|
| 15|        TABLE ACCESS FULL            |CT_MANAGE        |88505|2938K|       | 1621  (2)|00:00:20|
|*16|        HASH JOIN OUTER              |                 |39191|5166K|  4024K|10685  (2)|00:02:09|
|*17|         HASH JOIN                   |                 |39191|3559K|  2952K| 5622  (1)|00:01:08|
| 18|          INLIST ITERATOR            |                 |     |     |       |          |        |
| 19|          TABLE ACCESS BY INDEX ROWID|ARAP_DJFB        |39191|2487K|       | 2740  (1)|00:00:33|
|*20|            INDEX RANGE SCAN         |IDX_JSZC1026     |39212|     |       |   43  (3)|00:00:01|
|*21|          TABLE ACCESS FULL          |ARAP_DJZB        | 127K|3476K|       | 2494  (2)|00:00:30|
| 22|         TABLE ACCESS FULL           |CT_MANAGE_B      | 266K|  10M|       | 4179  (2)|00:00:51|
| 23|  VIEW                               |                 |88480|1900K|       |17802  (1)|00:03:34|
| 24|   HASH GROUP BY                     |                 |88480|  10M|    16M|17802  (1)|00:03:34|
|*25|    HASH JOIN                        |                 | 120K|  14M|  5024K|14906  (1)|00:02:59|
|*26|     TABLE ACCESS FULL               |PO_INVOICE_B     | 138K|3389K|       | 5263  (1)|00:01:04|
|*27|     HASH JOIN RIGHT OUTER           |                 |98165|   9M|  2856K| 8850  (2)|00:01:47|
| 28|      INDEX FAST FULL SCAN           |PK_CT_MANAGE     |88505|1815K|       |  107  (2)|00:00:02|
|*29|      HASH JOIN OUTER                |                 |98165|8052K|  5184K| 8154  (2)|00:01:38|
|*30|       TABLE ACCESS FULL             |PO_ORDER_B       |98165|4026K|       | 3035  (1)|00:00:37|
| 31|       TABLE ACCESS FULL             |CT_MANAGE_B      | 266K|  10M|       | 4179  (2)|00:00:51|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."PK_CT_MANAGE"(+)="A"."PK_CT_MANAGE")
   4 - access("CTV"."PK_CT_MANAGE"(+)="CTH"."PK_CT_MANAGE" AND
              "CTV"."PK_CT_MANAGE_B"(+)="CTB"."PK_CT_MANAGE_B")
   8 - filter("CTH"."DR"=0 AND "CTH"."ACTIVEFLAG"=0)
   9 - access("CTH"."CT_CODE"=:1)
  10 - access("CTB"."PK_CT_MANAGE"="CTH"."PK_CT_MANAGE")
  11 - filter("CTB"."PK_CORP"=:2 AND "CTB"."DR"=0)
  14 - access("CTH"."PK_CT_MANAGE"(+)="CTB"."PK_CT_MANAGE")
  16 - access("CTB"."PK_CT_MANAGE_B"(+)="A"."ZYX5")
  17 - access("A"."VOUCHID"="B"."VOUCHID")
  20 - access(("A"."JSFSBM"='D1' OR "A"."JSFSBM"='Z2' OR "A"."JSFSBM"='Z5') AND "A"."DJLXBM"='D3' AND
              "A"."DR"=0)
  21 - filter("B"."DR"=0 AND "B"."DJZT"<>1 AND "B"."DJZT"<>(-99))
  25 - access("FP"."CSOURCEBILLROWID"="DD"."CORDER_BID")
  26 - filter("FP"."CSOURCEBILLROWID" IS NOT NULL AND "FP"."DR"=0)
  27 - access("CT"."PK_CT_MANAGE"="CTH"."PK_CT_MANAGE"(+))
  29 - access("CT"."PK_CT_MANAGE_B"(+)="DD"."CSOURCEROWID")
  30 - filter("DD"."CUPSOURCEBILLTYPE"='Z2')
60 rows selected.

如图 9-13 所示，做一笔单据在后台要多次调用这个语句。

烂用外连接导致无法谓词推入 - 图2

图 9-13

100 个 SQL 语句每个执行 2.6 秒，全部执行就要 260 秒，将近 4 分钟。到这里，甲骨文的朋友问能否进一步优化该 SQL。

下面是分析过程。

在尝试优化 SQL 之前，首先询问该 SQL 返回多少行数据，甲骨文的朋友回答返回 1 行数据。在进行 SQL 优化的时候，我们必须知道一个 SQL 最终应该返回多少行数据，因为知道了 SQL 最终返回数据，就能判断表连接究竟是采用嵌套循环还是采用 HASH 连接，这至关重要。因为 SQL 最终返回一行数据，所以判断 SQL 的执行计划应该走嵌套循环。但是本 SQL 执行计划中几乎全是 HASH 连接。根据 SQL 语句过滤条件入手，一步一步分析执行计划，看哪里出了问题。

SQL 语句的过滤条件是WHERE view_xj_ct.ct_code = :1 AND view_xj_ct.pk_corp = :2。

这两个过滤条件已经在书中用阴影部分标注，为了方便读者查看现将其摘抄下来。

select cth.ct_code,
               cth.pk_ct_manage,
               sum(ctb.oritaxsummny) ybjshj,
               sum(ctv.ljfk) fkhj,
               ctb.pk_corp
          from ct_manage_b ctb
          left join ct_manage cth
            on ctb.pk_ct_manage = cth.pk_ct_manage
          left join view_xj_ct_fukuan ctv
            on ctv.pk_ct_manage_b = ctb.pk_ct_manage_b
           and ctv.pk_ct_manage = cth.pk_ct_manage
         where activeflag = 0
           and cth.dr = 0
           and ctb.dr = 0
         group by cth.ct_code, cth.pk_ct_manage, ctb.pk_corp

过滤条件分别针对 cth 和 ctb 进行过滤，执行计划中 Id=9 走的是 cth.ct_code 的索引，这说明此处发生了常量谓词推入，将过滤条件（常量过滤条件）推入到视图中进行了过滤。Id=9 属于 cth，它与 id=10（ctb）走的是嵌套循环。cth 与 ctb 关联的结果集在执行计划中是 Id=5 这步，Id=5 与 Id=12（view_xj_ct_fukuan）进行的是 HASH 连接。Id=12 是一个视图。因为该 SQL 最终只返回 1 行数据，应该全走嵌套循环才对，但是关联到视图 view_xj_ct_fukuan 的时候居然走的是 HASH 连接，所以笔者判断 Id=5 与 Id=12 关联方式出错。SQL 语句中，视图 view_xj_ct_fukuan 的别名是 ctv，ctv 分别与 cth 和 ctb 进行了关联。

left join view_xj_ct_fukuan ctv
   on ctv.pk_ct_manage_b = ctb.pk_ct_manage_b
 and ctv.pk_ct_manage = cth.pk_ct_manage

如果能让 cth 与 ctb 关联之后得到的结果集通过 ctv 的连接列传值给 ctv，通过连接列将数据将数据推入到视图中，这样就可以让视图走嵌套循环了，这种方式就是连接列谓词推入，但是执行计划并没有这样做。

于是查看如下视图 view_xj_ct_fukuan 的源代码。

CREATE OR REPLACE FORCE VIEW "JXNC"."VIEW_XJ_CT_FUKUAN" ("DDHH", "PK_CORP", "PK_CT_MANAGE_B", "PK_CT_MANAGE", "LJFK", "CT_CODE") AS 
  select ddhh,
       a.pk_corp,
       a.pk_ct_manage_b,
       ctb.pk_ct_manage,
       sum(a.ljfk) ljfk,
       cth.ct_code
  from (select a.ddhh,
                a.dwbm pk_corp,
                a.zyx5 pk_ct_manage_b,
                a.jfybje ljfk
          from arap_djfb a
          left join arap_djzb b on a.vouchid = b.vouchid
         where a.dr = 0
           and b.dr = 0
           and a.djlxbm = 'D3'
           and a.jsfsbm in ('Z2', 'Z5','D1')
           and b.djzt not in ('-99', '1')) a
  left join ct_manage_b ctb on ctb.pk_ct_manage_b = a.pk_ct_manage_b
  left join ct_manage cth on cth.pk_ct_manage = ctb.pk_ct_manage
 group by ddhh, a.pk_corp, a.pk_ct_manage_b, ctb.pk_ct_manage, cth.ct_code
 order by a.pk_ct_manage_b;

视图 ctv.pk_ct_manage 字段来自于 ctb，而 ctb 与 a 是外连接，而且 ctb 是从表，并不是主表。

正是因为 ctb 是视图中外连接的从表，而且视图 ctv 也是外连接的从表，所以导致 cth 不能通过连接列 pk_ct_manage 将谓词推入到 ctv.pk_ct_manage 中，从而导致走了 HASH 连接。

left join view_xj_ct_fukuan ctv
   on ctv.pk_ct_manage_b = ctb.pk_ct_manage_b
 and ctv.pk_ct_manage = cth.pk_ct_manage

如果能将视图中的外连接改成内连接，就可以将谓词推入到 ctv 中，从而走嵌套循环。

通过反复分析 SQL 写法，我们确认可以将视图中的外连接改写为内连接。于是新建了一个视图，专门用于本 SQL，将外连接改写为内连接，而且将后面的子查询也改成了内连接。最终 SQL 能在 0.01 秒内执行完毕，执行 100 个 SQL 也仅需耗时 1 秒，从而将原本要执行 4 分钟的单据业务优化到 1 秒。

接下来，我们通过实验为大家模拟当时情况。

create table emp_new as select * from emp;

Table created.

create index idx_ename on emp(ename);

Index created.

视图（e）里面表关联是外连接，而且视图（e）作为外连接从表，视图（e）连接列来自从表。

select /*+  push_pred(e) */ *
  from emp_new a
  left join (select d.dname, e.ename, sum(e.sal) total_sal
               from dept d
               left join emp e on d.deptno = e.deptno
               group by dname, ename) e on a.ename = e.ename
 where empno = 7900;

执行计划如下。

 select /*+  push_pred(e) */ *
   from emp_new a
   left join (select d.dname, e.ename, sum(e.sal) total_sal
                 from dept d
                left join emp e on d.deptno = e.deptno
                group by dname, ename) e on a.ename = e.ename
  where empno = 7900;

Execution Plan
----------------------------------------------------------
Plan hash value: 3023292314
-------------------------------------------------------------------------------------
| Id|Operation                      |Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0|SELECT STATEMENT               |       |     1 |   116 |    10  (30)| 00:00:01 |
|* 1| HASH JOIN OUTER               |       |     1 |   116 |    10  (30)| 00:00:01 |
|* 2|  TABLE ACCESS FULL            |EMP_NEW|     1 |    87 |     2   (0)| 00:00:01 |
|  3|  VIEW                         |       |    14 |   406 |     7  (29)| 00:00:01 |
|  4|   HASH GROUP BY               |       |    14 |   364 |     7  (29)| 00:00:01 |
|  5|    MERGE JOIN OUTER           |       |    14 |   364 |     6  (17)| 00:00:01 |
|  6|    TABLE ACCESS BY INDEX ROWID|DEPT   |     4 |    52 |     2   (0)| 00:00:01 |
|  7|      INDEX FULL SCAN          |PK_DEPT|     4 |       |     1   (0)| 00:00:01 |
|* 8|     SORT JOIN                 |       |    14 |   182 |     4  (25)| 00:00:01 |
|  9|      TABLE ACCESS FULL        |EMP    |    14 |   182 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ENAME"="E"."ENAME"(+))
   2 - filter("A"."EMPNO"=7900)
   8 - access("D"."DEPTNO"="E"."DEPTNO"(+))
       filter("D"."DEPTNO"="E"."DEPTNO"(+))

当视图里面表关联是外连接，而且视图与其他表关联作为外连接从表，视图连接列来自视图里面的从表，此时不能谓词推入。

我们将视图里面表关联改成内连接。

select /*+  push_pred(e) */ *
  from emp_new a
  left join (select d.dname, e.ename, sum(e.sal) total_sal
               from dept d
                join emp e on d.deptno = e.deptno
              group by dname, ename) e on a.ename = e.ename
 where empno = 7900;

执行计划如下。

 select /*+  push_pred(e) */ *
    from emp_new a
    left join (select d.dname, e.ename, sum(e.sal) total_sal
                 from dept d
                  join emp e on d.deptno = e.deptno
                group by dname, ename) e on a.ename = e.ename
   where empno = 7900;

Execution Plan
----------------------------------------------------------
Plan hash value: 3258229530
-------------------------------------------------------------------------------------
| Id|Operation                       |Name     |Rows | Bytes | Cost(%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0|SELECT STATEMENT                |         |    1|   111 |    6  (17)| 00:00:01 |
|  1| NESTED LOOPS OUTER             |         |    1|   111 |    6  (17)| 00:00:01 |
|* 2|  TABLE ACCESS FULL             |EMP_NEW  |    1|    87 |    2   (0)| 00:00:01 |
|  3|  VIEW PUSHED PREDICATE         |         |    1|    24 |    4  (25)| 00:00:01 |
|  4|   SORT GROUP BY                |         |    1|    26 |    4  (25)| 00:00:01 |
|  5|    NESTED LOOPS                |         |     |       |           |          |
|  6|     NESTED LOOPS               |         |    1|    26 |    3   (0)| 00:00:01 |
|  7|     TABLE ACCESS BY INDEX ROWID|EMP      |    1|    13 |    2   (0)| 00:00:01 |
|* 8|       INDEX RANGE SCAN         |IDX_ENAME|    1|       |    1   (0)| 00:00:01 |
|* 9|      INDEX UNIQUE SCAN         |PK_DEPT  |    1|       |    0   (0)| 00:00:01 |
| 10|     TABLE ACCESS BY INDEX ROWID|DEPT     |    1|    13 |    1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."EMPNO"=7900)
   8 - access("E"."ENAME"="A"."ENAME")
   9 - access("D"."DEPTNO"="E"."DEPTNO")

将视图里面的外连接改成内连接之后，我们就可以将谓词推入到视图中了。

如果不改视图中的外连接，将 SQL 语句中的外连接改成内连接也可以将谓词推入视图。

select /*+  push_pred(e) */ *
  from emp_new a
   join (select d.dname, e.ename, sum(e.sal) total_sal
               from dept d
               left join emp e on d.deptno = e.deptno
              group by dname, ename) e on a.ename = e.ename
 where empno = 7900;

执行计划如下。

 select /*+  push_pred(e) */ *
    from emp_new a
     join (select d.dname, e.ename, sum(e.sal) total_sal
                 from dept d
                 left join emp e on d.deptno = e.deptno
                group by dname, ename) e on a.ename = e.ename
   where empno = 7900;

Execution Plan
----------------------------------------------------------
Plan hash value: 3747089680
-------------------------------------------------------------------------------------
| Id|Operation                      |Name     | Rows | Bytes | Cost(%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0|SELECT STATEMENT               |         |    1 |   125 |    5  (20)| 00:00:01 |
|  1| HASH GROUP BY                 |         |    1 |   125 |    5  (20)| 00:00:01 |
|  2|  NESTED LOOPS                 |         |      |       |           |          |
|  3|   NESTED LOOPS                |         |    1 |   125 |    4   (0)| 00:00:01 |
|  4|    NESTED LOOPS               |         |    1 |   112 |    3   (0)| 00:00:01 |
|* 5|     TABLE ACCESS FULL         |EMP_NEW  |    1 |    99 |    2   (0)| 00:00:01 |
|  6|    TABLE ACCESS BY INDEX ROWID|EMP      |    1 |    13 |    1   (0)| 00:00:01 |
|* 7|      INDEX RANGE SCAN         |IDX_ENAME|    1 |       |    0   (0)| 00:00:01 |
|* 8|    INDEX UNIQUE SCAN          |PK_DEPT  |    1 |       |    0   (0)| 00:00:01 |
|  9|   TABLE ACCESS BY INDEX ROWID |DEPT     |    1 |    13 |    1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("A"."EMPNO"=7900)
   7 - access("A"."ENAME"="E"."ENAME")
   8 - access("D"."DEPTNO"="E"."DEPTNO")

笔者当时究竟是怎么判断可以将 view_xj_ct_fukuan ctv 里面的视图改成内连接的呢？

请大家注意观察原始 view_xj_ct 部分代码。

select cth.ct_code,
               cth.pk_ct_manage,
               sum(ctb.oritaxsummny) ybjshj,
               sum(ctv.ljfk) fkhj,
               ctb.pk_corp
          from ct_manage_b ctb
          left join ct_manage cth
            on ctb.pk_ct_manage = cth.pk_ct_manage
          left join view_xj_ct_fukuan ctv
            on ctv.pk_ct_manage_b = ctb.pk_ct_manage_b
           and ctv.pk_ct_manage = cth.pk_ct_manage
         where activeflag = 0
           and cth.dr = 0
           and ctb.dr = 0
         group by cth.ct_code, cth.pk_ct_manage, ctb.pk_corp

注意观察阴影部分连接条件，视图 ctv 中的连接列也是来自 cth 和 ctb。

CREATE OR REPLACE FORCE VIEW "JXNC"."VIEW_XJ_CT_FUKUAN" ("DDHH", "PK_CORP", "PK_CT_MANAGE_B", "PK_CT_MANAGE", "LJFK", "CT_CODE") AS 
  select ddhh,
       a.pk_corp,
       a.pk_ct_manage_b,
       ctb.pk_ct_manage,
       sum(a.ljfk) ljfk,
       cth.ct_code
  from (select a.ddhh,
                a.dwbm pk_corp,
                a.zyx5 pk_ct_manage_b,
                a.jfybje ljfk
          from arap_djfb a
          left join arap_djzb b on a.vouchid = b.vouchid
         where a.dr = 0
           and b.dr = 0
           and a.djlxbm = 'D3'
           and a.jsfsbm in ('Z2', 'Z5','D1')
           and b.djzt not in ('-99', '1')) a
  left join ct_manage_b ctb on ctb.pk_ct_manage_b = a.pk_ct_manage_b
  left join ct_manage cth on cth.pk_ct_manage = ctb.pk_ct_manage
 group by ddhh, a.pk_corp, a.pk_ct_manage_b, ctb.pk_ct_manage, cth.ct_code
 order by a.pk_ct_manage_b;

同时视图 ctv 中有对连接列进行汇总，这其实相当于如下 SQL。

select e.empno, sum(sum_sal)
  from emp e
  left join (select d.deptno, sum(sal) sum_sal
               from dept d
               left join emp e on d.deptno = e.deptno
              group by d.deptno) d on e.deptno = d.deptno
 group by empno;

上面 SQL 可以安全地将 left join 改写为 inner join。

select e.empno, sum(sum_sal)
  from emp e
  left join (select d.deptno, sum(sal) sum_sal
               from dept d
               join emp e on d.deptno = e.deptno
              group by d.deptno) d on e.deptno = d.deptno
 group by empno;

同理，原始 SQL 中后面的子查询也能改写为 inner join。

想要优化本案例中的 SQL，必须具备较强的 SQL 优化能力以及较强的 SQL 改写能力，这两种能力缺一不可。通过本案例，我们也要反思，为什么开发人员在 SQL 中一直写 left join？我们甚至怀疑是不是开发人员只会 left join，或者不管写什么 SQL，一直 left join，这太可怕了，由此可见，在系统上线之前，SQL 审核是多么重要！