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

最近遇到一个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

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

| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)|
Time     |

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

|   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  的效率比较,    我只是说道  有时候 不精准, 应该 看 逻辑读数量。   


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






















 

 


 


展开阅读全文

没有更多推荐了,返回首页