SQL改写，聚合语句， left join 改写标量子查询

来源：互联网发布：python高级编程最新版编辑：程序博客网时间：2024/05/17 07:39

最近遇到一个SQL 改写，其实改写难度一般，但连续强调用 left join 改写标量子查询，不知道为何如此多的标量子查询

案例说明：为了公司机密，所以用scott 用户表示

select d.*, (select count(*) from scott.emp) cnt1,
     (select count(*) from scott.emp p where p.deptno = d.deptno) cnt2,
     (select count(*) from scott.emp p where p.deptno = d.deptno and p.job='CLERK') ccnt,
     (select count(*) from scott.emp p where p.deptno = d.deptno and p.job='SALESMAN') scnt
    from scott.dept d;

   select d.deptno, d.dname, d.loc, sum(decode(count(*), 1, 0, count(*))) over() cnt1 ,
      decode(count(*), 1, 0, count(*)) cnt2 ,
      count( case when p.job='CLERK' then 1 else null end) ccnt,
      count( case when p.job='SALESMAN' then 1 else null end) scnt
      from scott.dept d
    left join scott.emp p on d.deptno = p.deptno
     group by d.deptno, d.dname, d.loc, d.deptno ;

decode(count(*), 1, 0, count(*)) cnt2 , 由于是left join 所以这里是 1 就是 0 。

count( case when p.job='CLERK' then 1 else null end) ccnt, 就是统计 'CLERK' 的数量，类似 (select count(*) from scott.emp p where p.deptno = d.deptno and p.job='CLERK') ccnt,

结果测试数据完全相同。效率提升 10倍。

第一个的执行计划

-----------------------------------------------------------------------------
| Id | Operation          | Name   | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     4 |    80 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |        |     1 |       |            |          |
|   2 |   INDEX FULL SCAN | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|   3 | SORT AGGREGATE    |        |     1 |     3 |            |          |
|* 4 |   TABLE ACCESS FULL| EMP    |     5 |    15 |     3   (0)| 00:00:01 |
|   5 | SORT AGGREGATE    |        |     1 |    11 |            |          |
|* 6 |   TABLE ACCESS FULL| EMP    |     1 |    11 |     3   (0)| 00:00:01 |
|   7 | SORT AGGREGATE    |        |     1 |    11 |            |          |
|* 8 |   TABLE ACCESS FULL| EMP    |     1 |    11 |     3   (0)| 00:00:01 |
|   9 | TABLE ACCESS FULL | DEPT   |     4 |    80 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("P"."DEPTNO"=:B1)
   6 - filter("P"."JOB"='CLERK' AND "P"."DEPTNO"=:B1)
   8 - filter("P"."JOB"='SALESMAN' AND "P"."DEPTNO"=:B1)

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
         93 consistent gets
          0 physical reads
          0 redo size
       1098 bytes sent via SQL*Net to client
        519 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          4 rows processed

第二个的执行计划

执行计划
----------------------------------------------------------
Plan hash value: 2617737508

--------------------------------------------------------------------------------
----------

--------------------------------------------------------------------------------
----------

| 0 | SELECT STATEMENT | | 14 | 434 | 7 (29)|
00:00:01 |

| 1 | WINDOW BUFFER | | 14 | 434 | 7 (29)|
00:00:01 |

| 2 | HASH GROUP BY | | 14 | 434 | 7 (29)|
00:00:01 |

| 3 | MERGE JOIN OUTER | | 14 | 434 | 6 (17)|
00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)|
00:00:01 |

| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)|
00:00:01 |

|* 6 | SORT JOIN | | 14 | 154 | 4 (25)|
00:00:01 |

| 7 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)|
00:00:01 |

--------------------------------------------------------------------------------
----------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("D"."DEPTNO"="P"."DEPTNO"(+))
filter("D"."DEPTNO"="P"."DEPTNO"(+))

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          9 consistent gets
          0 physical reads
          0 redo size
       1094 bytes sent via SQL*Net to client
        519 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
          4 rows processed

SQL>

93 逻辑 VS 9 个逻辑读。期间和一个高级程序员交流他说在 PL/SQL 中的花费时间来判断 SQL 的效率比较，我只是说道有时候不精准，应该看逻辑读数量。

反正我关心的是执行计划的统计信息。

0 0

SQL改写， 聚合语句， left join 改写标量子查询

SQL改写，聚合语句， left join 改写标量子查询