Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ GROUP BY extension     - Oracle 11g maintenance partitions (eight) - Renaming Partitions (Database)

- Embedded Linux Optimization (Programming)

- Ubuntu 14.04 forget solution root password (Linux)

- Construction of Android and iOS apps use Jenkins Automation (Programming)

- EXP-00091 Error resolved (Database)

- JSON data normalization (normalize) (Programming)

- Oracle Linux 5.9 configuration Xmanager (Linux)

- Linux shell script debugging (Linux)

- IntelliJ IDEA run in Mac10.9 and JDK7 environment (Linux)

- The YUM package management under Linux (Linux)

- JavaScript function definition mode (Programming)

- Analysis of common mistakes when compiling MySQL installation (Database)

- CRF ++ Linux use (Linux)

- Advanced permissions Linux file system settings (Linux)

- MySQL fuzzy query: LIKE and REGEXP pattern mode (Database)

- 8 Docker knowledge you may not know (Server)

- Linux md5sum verify file integrity (Linux)

- ActiveMQ configuration Getting Started Tutorial (Server)

- Oracle large table to clean truncate .. reuse storage (Database)

- The Linux C truncate function clears the file notes (Programming)

 
         
  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:      
 
- Fedora 21 setting boot script (Linux)
- Github Remote Assistance (Linux)
- Tsunami-udp accelerated network transmission (Linux)
- Use Redis as time-series database: why and how (Database)
- Android realize RippleEffect water (Programming)
- Using iptables achieve NAT (Linux)
- Bitmap memory footprint of computing Android memory optimization (Linux)
- Detailed installation OpenCV2.3.1 under CentOS 6.5 (Linux)
- Linux character device - a simple character device model (Linux)
- Deepin Tutorial: Depth Description Installer expert mode (Linux)
- Taught you how to build your own VPS server (Server)
- To install and deploy Java applications under CentOS 6.5 (Linux)
- OpenJDK7 source compiler installation on CentOS 6.5 (Linux)
- Protect against network attacks using Linux system firewall (Linux)
- Linux System Getting Started Learning: From VirtualBox from the client host access NAT (Linux)
- Docker data container protection the pros and cons (Server)
- U disk to install Windows and Ubuntu 15.04 dual system (Programming)
- ORA-4031 error Solution (Database)
- Hive start being given: Found class jline.Terminal, but interface was expected (Database)
- Zabbix monitoring platform to extract a single server graphical Python implementation of concurrent (Server)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.