Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ GROUP BY extension     - Raspberry Pi 2 to install the latest version of the FPC and Lazarus 1.5 (Linux)

- Red Hat Linux mount U disk (Linux)

- Linux host dual LAN transceiver package ARP problem (Linux)

- socket busy poll of Linux kernel 3.11 to avoid sleep switch (Linux)

- ActiveMQ-based shared file system HA solutions (Server)

- Detailed iptables (Linux)

- Ubuntu how to install and use Objective-C (Linux)

- How to install new fonts on Ubuntu 14.04 and 14.10 (Linux)

- How Vim playing a mature IDE (Linux)

- CentOS yum install LNMP PHP5.4 version (Server)

- innodb storage engine backup tool --Xtrabackup (Database)

- How to Install Winusb in Ubuntu 14.04 (Linux)

- expdp / impdp use version parameter data migration across versions (Database)

- CentOS 6.5 set under Oracle 12c at startup (Database)

- Java in several ways of using MongoDB (Programming)

- RedHat Redis Linux installation (Database)

- Linux kernel modules related to the management Comments (Linux)

- Android child thread really do not update UI (Programming)

- MySQL query performance comparison of a single truth (Database)

- Novice Advanced Ubuntu7.10 configured IP address (Linux)

 
         
  GROUP BY extension
     
  Add Date : 2018-11-21      
         
       
         
  GROUP BY extensions include ROLLUP, CUBE, GROUPING SETS three forms.

ROLLUP

rollup versus a simple grouping together increases the subtotal and total, it would be more abstract interpretation, let's look at the specific case.

Example 1, the sum of the sum of the different statistical sector wages and salaries in all sectors.

SQL> select deptno, sum (sal) from emp group by rollup (deptno);

    DEPTNO SUM (SAL)
---------- ----------
        108 750
        2010875
        309 400
                29025
Example 2, in this case on the deptno first group, and then the job grouping

SQL> select deptno, job, sum (sal) from emp group by rollup (deptno, job);

    DEPTNO JOB SUM (SAL)
---------- --------- ----------
        10 CLERK 1300 --10 number is the sum CLERK JOB sector wages
        10 MANAGER 2450
        10 PRESIDENT 5000
        The sum of all types of wages 108750--10 No.
        20 CLERK 1900
        20 ANALYST 6000
        20 MANAGER 2975
        2010875
        30 CLERK 950
        30 MANAGER 2850
        30 SALESMAN 5600
        309 400
                          The sum of all types of wages in all sectors --29 025
13 rows selected.
If you use an ordinary grouping functions, and can be used UNION ALL statement:

The sum of the individual sectors to achieve individual types of wages -
select deptno, job, sum (sal) from emp group by deptno, job
union all
- The sum of the individual sectors to achieve wage
select deptno, null, sum (sal) from emp group by deptno
union all
- The sum of all sectors to achieve wage
select null, null, sum (sal) from emp
order by 1,2
Here we were to look at both the planning and execution statistics,

ROLLUP statement:

Execution Plan
-------------------------------------------------- ---------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- ---------------------------
| 0 | SELECT STATEMENT | | 11 | 132 | 3 (34) | 00:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 11 | 132 | 3 (34) | 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 168 | 2 (0) | 00:00:01 |
-------------------------------------------------- ---------------------------
Statistics
-------------------------------------------------- --------
      0 recursive calls
      0 db block gets
      2 consistent gets
      0 physical reads
      0 redo size
    895 bytes sent via SQL * Net to client
    519 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 statement:

Execution Plan
-------------------------------------------------- ---------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- ---------------------------
| 0 | SELECT STATEMENT | | 15 | 150 | 9 (34) | 00:00:01 |
| 1 | SORT ORDER BY | | 15 | 150 | 8 (75) | 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | HASH GROUP BY | | 11 | 132 | 3 (34) | 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 168 | 2 (0) | 00:00:01 |
| 5 | HASH GROUP BY | | 3 | 15 | 3 (34) | 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 70 | 2 (0) | 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 3 | | |
| 8 | TABLE ACCESS FULL | EMP | 14 | 42 | 2 (0) | 00:00:01 |
-------------------------------------------------- ---------------------------
Statistics
-------------------------------------------------- --------
      0 recursive calls
      0 db block gets
      6 consistent gets
      0 physical reads
      0 redo size
    895 bytes sent via SQL * Net to client
    519 bytes received via SQL * Net from client
      2 SQL * Net roundtrips to / from client
      1 sorts (memory)
      0 sorts (disk)
     13 rows processed
Easy to see, to achieve the same functionality, ROLLUP respect UNION ALL efficiency has been greatly improved.

CUBE

cube with respect to the rollup, and outputs the result in greater detail.

Example 1, in this case, is not obvious.

SQL> select deptno, sum (sal) from emp group by cube (deptno);

    DEPTNO SUM (SAL)
---------- ----------
                29025
        108 750
        2010875
        309 400
Example 2, with respect to the rollup, cube also trades this column for a special summary.

SQL> select deptno, job, sum (sal) from emp group by cube (deptno, job);

    DEPTNO JOB SUM (SAL)
---------- --------- ----------
                          29025
           CLERK 4150
           ANALYST 6000
           MANAGER 8275
           SALESMAN 5600
           PRESIDENT 5000
       108 750
       10 CLERK 1300
       10 MANAGER 2450
       10 PRESIDENT 5000
       2010875
       20 CLERK 1900
       20 ANALYST 6000
       20 MANAGER 2975
       309 400
       30 CLERK 950
       30 MANAGER 2850
       30 SALESMAN 5600
18 rows selected.
GROUPING SETS

GROUPING SETS ROLLUP respect and CUBE, the result is a statistical classification, better readability.

example 1:

SQL> select deptno, job, to_char (hiredate, 'yyyy') hireyear, sum (sal) from emp group by grouping sets (deptno, job, to_char (hiredate, 'yyyy'));

    DEPTNO JOB HIRE SUM (SAL)
---------- --------- ---- ----------
           CLERK 4150
           SALESMAN 5600
           PRESIDENT 5000
           MANAGER 8275
           ANALYST 6000
       309 400
       2010875
       108 750
                        19874100
                        1980800
                        19821300
                        198122825
Example 2:

SQL> select deptno, job, sum (sal) from emp group by grouping sets (deptno, job);

    DEPTNO JOB SUM (SAL)
---------- --------- ----------
           CLERK 4150
           SALESMAN 5600
           PRESIDENT 5000
           MANAGER 8275
           ANALYST 6000
        309 400
        2010875
        108 750
8 rows selected.
For this example, use UNION ALL how to achieve it?

select null deptno, job, sum (sal) from emp group by job
union all
select deptno, null, sum (sal) from emp group by deptno;
Both execution plans and statistics are as follows:

GROUPING SETS:

Execution Plan
-------------------------------------------------- -------------------------------------------------- ----
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- -------------------------------------------------- ----
| 0 | SELECT STATEMENT | | 11 | 352 | 10 (20) | 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6795_E71F79 | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 168 | 2 (0) | 00:00:01 |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9D6796_E71F79 | | | | |
| 5 | HASH GROUP BY | | 1 | 19 | 3 (34) | 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6795_E71F79 | 1 | 19 | 2 (0) | 00:00:01 |
| 7 | LOAD AS SELECT | SYS_TEMP_0FD9D6796_E71F79 | | | | |
| 8 | HASH GROUP BY | | 1 | 26 | 3 (34) | 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6795_E71F79 | 1 | 26 | 2 (0) | 00:00:01 |
| 10 | VIEW | | 1 | 32 | 2 (0) | 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6796_E71F79 | 1 | 32 | 2 (0) | 00:00:01 |
-------------------------------------------------- -------------------------------------------------- ----
Statistics
-------------------------------------------------- --------
      4 recursive calls
     24 db block gets
     17 consistent gets
      3 physical reads
   1596 redo size
    819 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)
      8 rows processed
UNION ALL:

-------------------------------------------------- --------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- --------------------------
| 0 | SELECT STATEMENT | | 8 | 65 | 6 (67) | 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | HASH GROUP BY | | 5 | 50 | 3 (34) | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0) | 00:00:01 |
| 4 | HASH GROUP BY | | 3 | 15 | 3 (34) | 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 70 | 2 (0) | 00:00:01 |
-------------------------------------------------- --------------------------

Statistics
-------------------------------------------------- --------
      0 recursive calls
      0 db block gets
      4 consistent gets
      0 physical reads
      0 redo size
    819 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)
      8 rows processed
And rollup difference is, grouping sets even lower efficiency than the same function union all statements that achieve a little unexpected. Opinion, can not be applied blindly Oracle provides solutions, at least in this case is so.
     
         
       
         
  More:      
 
- Linux unpack the tar file to a different directory (Linux)
- How to find the available network adapter on Ubuntu (Linux)
- How to install and configure a VNC server on CentOS 7.0 (Server)
- Stunning exclamation point at the Linux command line (Linux)
- Shell Scripting Interview Questions (Programming)
- Oracle multi-user concurrency and transaction processing (Database)
- HDFS Hadoop Distributed File System Works (Server)
- Ubuntu resolve sudo: source: command not found error (Linux)
- Linux disk partition, format, mount the directory (Linux)
- Linux Network Programming - non-blocking program (Programming)
- Installation and use GAMIT / GLOBK Software (Linux)
- Customize the 404 error page Nginx (Server)
- Nginx Installation and Configuration (Server)
- How do I delete a NEEDS RECOVERY rollback state of undo tablespace (Database)
- JavaScript event handling Detailed (Programming)
- Docker installation under CentOS7 (Linux)
- Zabbix system email alert Python script (Server)
- ld.so.conf.d profile (Linux)
- AngularJS - Getting Started with Routing (Programming)
- Redis data types Introduction (Database)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.