rolllup巧用

Predicate Information (identified by operation id):

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

Plan hash value: 1037965942

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


计算音讯

          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(假若您想再多3个维度,比如再追加雇佣年份的总计,以前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      
                                   

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

00:00:01 |

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

Plan hash value: 1037965942

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

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

—写法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

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

/*

    |

|   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 |

    |

|   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)|

DNAME          JOB          SUM_SAL

union all 合并笨办法暴发的实践安顿

不错不错,自小编陶醉中….
   停!先别得意,有人跑来说希望能增添一列总的汇总。
      等等,更变态的须求来了,希望能博取差距DNAME的分别汇总!


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;

Predicate Information (identified by operation id):

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

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)|

—写法2(不可以,先想到如下多个方法来促成楼上的变态须求)


*/

00:00:01 |

计算消息

          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写法时有发生的举办安插

Predicate Information (identified by operation id):

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

                              

部分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); 

履行陈设

计算音讯

          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
         

–看官们注意到了吗,多了1个维度的统计,无论是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   
                              
                              

Plan hash value: 2979078843

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

相关文章