rolllup巧用

–构造环境
drop table dept purge;
drop table emp purge;
create table dept as select * from scott.dept;
create table emp  as select * from scott.emp;


set term off
set heading on
set verify off
set feedback off
set linesize 2000
set pagesize 30000
set long 999999999
set longchunksize 999999
set autotrace off

—写法1
SELECT  a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname,b.job;

DNAME          JOB          SUM_SAL


SALES          MANAGER         2850
SALES          CLERK            950
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000
ACCOUNTING     CLERK           1300
SALES          SALESMAN        5600
RESEARCH       MANAGER         2975
RESEARCH       ANALYST         6000
RESEARCH       CLERK           1900

/*

不错不错,自我陶醉中….
   停!先转移得意,有人跑来说要能够多一排总的汇集。
      等等,更变态的需来了,希望会获不同DNAME的各自汇总!

*/

—写法2(没道,先想到如下一个计来兑现楼上的变态要求)

set autotrace on 
select * from (
SELECT  a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname,b.job
UNION ALL
–实现了部门的小计
SELECT  a.dname,NULL, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname
UNION ALL
–实现了富有单位到底的协议
SELECT  NULL,NULL, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno)
order by dname;

DNAME          JOB          SUM_SAL


ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000
ACCOUNTING                     8750
RESEARCH       CLERK           1900
RESEARCH       MANAGER         2975
RESEARCH       ANALYST         6000
RESEARCH                      10875
SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
SALES                          9400
                              29025

union all 合并笨办法产生的履计划

Plan hash value: 2979078843

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

Time     |

|   0 | SELECT STATEMENT       |      |    29 |   812 |    23  (22)|
00:00:01 |
|   1 |  SORT ORDER BY         |      |    29 |   812 |    23  (22)|
00:00:01 |
|   2 |   VIEW                 |      |    29 |   812 |    22  (19)|
00:00:01 |
|   3 |    UNION-ALL           |      |       |       |            |    
     |
|   4 |     HASH GROUP BY      |      |    14 |   756 |     8  (25)|
00:00:01 |
|*  5 |      HASH JOIN         |      |    14 |   756 |     7  (15)|
00:00:01 |
|   6 |       TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)|
00:00:01 |
|   7 |       TABLE ACCESS FULL| EMP  |    14 |   448 |     3   (0)|
00:00:01 |
|   8 |     HASH GROUP BY      |      |    14 |   672 |     8  (25)|
00:00:01 |
|*  9 |      HASH JOIN         |      |    14 |   672 |     7  (15)|
00:00:01 |
|  10 |       TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)|
00:00:01 |
|  11 |       TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)|
00:00:01 |
|  12 |     SORT AGGREGATE     |      |     1 |    39 |            |    
     |
|* 13 |      HASH JOIN         |      |    14 |   546 |     7  (15)|
00:00:01 |
|  14 |       TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)|
00:00:01 |
|  15 |       TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)|

00:00:01 |

Predicate Information (identified by operation id):

   5 – access(“A”.”DEPTNO”=”B”.”DEPTNO”)
   9 – access(“A”.”DEPTNO”=”B”.”DEPTNO”)
  13 – access(“A”.”DEPTNO”=”B”.”DEPTNO”)

统计信息

          0  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        783  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         13  rows processed
         

—写法3(学本领很重大,如果你会rollup神功,性能就能大提升,SQL书写也无累了)

set autotrace on 
SELECT  a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b 
WHERE a.deptno = b.deptno
GROUP  BY ROLLUP(a.dname,b.job);

DNAME          JOB          SUM_SAL


SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
SALES                          9400
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
RESEARCH                      10875
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000
ACCOUNTING                     8750
                              29025

rollup写法时有发生的实施计划

Plan hash value: 1037965942

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

    |

|   0 | SELECT STATEMENT     |      |    14 |   756 |     8  (25)|
00:00:01 |
|   1 |  SORT GROUP BY ROLLUP|      |    14 |   756 |     8  (25)|
00:00:01 |
|*  2 |   HASH JOIN          |      |    14 |   756 |     7  (15)|
00:00:01 |
|   3 |    TABLE ACCESS FULL | DEPT |     4 |    88 |     3   (0)|
00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |    14 |   448 |     3   (0)|

00:00:01 |

Predicate Information (identified by operation id):

   2 – access(“A”.”DEPTNO”=”B”.”DEPTNO”)

统计信息

          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         13  rows processed

–以此地应该可以知晓的觉察,表底顾次数比较union
all硬平畴的如果少,而且COST和逻辑读也不见之差不多!

—写法4(如果你想还多一个维度,比如更搭雇佣年份的统计,之前union
all硬拼凑的主意而倒了咔嚓,不过rollup轻松搞定,如下)

SELECT to_char(b.hiredate,’yyyy’) hire_year,a.dname,b.job, SUM(sal)
sum_sal
FROM dept a,emp b 
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(to_char(b.hiredate,’yyyy’),a.dname,b.job);

HIRE DNAME          JOB          SUM_SAL


1980 RESEARCH       CLERK            800
1980 RESEARCH                        800
1980                                 800
1981 SALES          CLERK            950
1981 SALES          MANAGER         2850
1981 SALES          SALESMAN        5600
1981 SALES                          9400
1981 RESEARCH       ANALYST         3000
1981 RESEARCH       MANAGER         2975
1981 RESEARCH                       5975
1981 ACCOUNTING     MANAGER         2450
1981 ACCOUNTING     PRESIDENT       5000
1981 ACCOUNTING                     7450
1981                               22825
1982 ACCOUNTING     CLERK           1300
1982 ACCOUNTING                     1300
1982                                1300
1987 RESEARCH       CLERK           1100
1987 RESEARCH       ANALYST         3000
1987 RESEARCH                       4100
1987                                4100
                                   29025      
                                   

推行计划

Plan hash value: 1037965942

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

    |

|   0 | SELECT STATEMENT     |      |    14 |   882 |     8  (25)|
00:00:01 |
|   1 |  SORT GROUP BY ROLLUP|      |    14 |   882 |     8  (25)|
00:00:01 |
|*  2 |   HASH JOIN          |      |    14 |   882 |     7  (15)|
00:00:01 |
|   3 |    TABLE ACCESS FULL | DEPT |     4 |    88 |     3   (0)|
00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |    14 |   574 |     3   (0)|

00:00:01 |

Predicate Information (identified by operation id):

   2 – access(“A”.”DEPTNO”=”B”.”DEPTNO”)

统计信息

          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1107  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         22  rows processed
         

–看官们注意到了邪,多了一个维度的统计,无论是COST还是逻辑读,都并未增加,够帅!
    

—写法5 (另外,不止是充实维度,更换维度的程序,对rollup
也是轻而易举的从事,如下)

SELECT  b.job,a.dname, SUM(b.sal) sum_sal
FROM dept a,emp b 
WHERE a.deptno = b.deptno
GROUP  BY ROLLUP(b.job,a.dname);

JOB       DNAME             SUM_SAL


CLERK     SALES                 950
CLERK     RESEARCH             1900
CLERK     ACCOUNTING           1300
CLERK                          4150
ANALYST   RESEARCH             6000
ANALYST                        6000
MANAGER   SALES                2850
MANAGER   RESEARCH             2975
MANAGER   ACCOUNTING           2450
MANAGER                        8275
SALESMAN  SALES                5600
SALESMAN                       5600
PRESIDENT ACCOUNTING           5000
PRESIDENT                      5000
                              29025   
                              
                              

                              

部分ROLLUP分组—————————————
SELECT to_char(b.hiredate,’yyyy’) hire_year,a.dname,b.job, SUM(sal)
sum_sal
FROM dept a,emp b 
WHERE a.deptno = b.deptno
GROUP BY to_char(b.hiredate,’yyyy’),a.dname,ROLLUP(b.job); 

相关文章