Oralce Advanced SQL Rollup and cube

In Oracle SQL queries, Oracle provides advanced features that simplify SQL query statements and improve query performance. The usage and differences of Rollup,cube are described below.

1. Rollup

Rollup can calculate subtotals and totals for multiple levels of a specified grouping field in a SELECT statement. The rollup is very easy to use and very efficient.
Rollup scans the grouped fields from right to left, gradually creating a higher-level subtotal. Finally, create a row total. Rollup will create a subtotal of n + 1 levels (levels), and n refers to the number of fields in the rollup.

1.1 When to use rollup

• Subtotals for some hierarchical dimensions (subtotal and grand totals for some hierarchical fields)
• For statistical summary tables in the Data Warehouse, rollup can simplify the statistical summary table and increase the speed of the Query Statistics summary table

1.2 Rollup Example

First, construct two basic table EMP (Employee table) with Dept (Department table):

Sql> CREATE TABLE EMP as SELECT * from Scott.emp; Table created. Sql> CREATE TABLE Dept as select * from Scott.dept; Table created.     Sql> select * from EMP;  EMPNO ename JOB MGR hiredate SAL COMM DEPTNO---------------------------------------                    ---------------------------------------7369 SMITH Clerk 7902 17-dec-80 800        7499 ALLEN salesman 7698 20-feb-81-7521 WARD salesman                    7698 22-feb-81 1250 7566 JONES MANAGER 7839 02-apr-81 2975      7654 MARTIN salesman 7698 28-sep-81 1250 1400 7698 BLAKE       Manager 7839 01-may-81 2850 7782 CLARK manager 7839 09-jun-81            2450 7788 SCOTT ANALYST 7566 19-apr-87 3000        7839 KING President 17-nov-81 7844 TURNER SALES                    Man 7698 08-sep-81 0 7876 ADAMS clerk 7788 23-may-87 1100       7900 JAMES Clerk 7698 03-dec-81 950 7902 FORD       ANALYST 7566 03-dec-81 7934 MILLER clerk 7782 23-jan-82 1300 1014 Rows selected.    Sql> select * FROM dept;       DEPTNO dname LOC-------------------------------------ten ACCOUNTING NEW YORK DALLAS SALES CHICAGO OPERATIONS BOSTON

If you want to count the total salary for each position in each department, the SQL statement is as follows:

Sql> Select B.dname, A.job, sum (a.sal)  2 from  emp A, Dept B  3  where A.deptno = B.deptno  4  Grou P by B.dname, a.job;dname          job       SUM (a.sal)---------------------------------SALES          MANAGER         2850SALES          Clerk            950ACCOUNTING     MANAGER         2450ACCOUNTING     President       5000ACCOUNTING     Clerk           1300SALES          salesman        5600RESEARCH       MANAGER         2975RESEARCH       ANALYST         6000RESEARCH       Clerk           19009 rows selected.

If you want to use an SQL statement to count the total salary for each position in each department , the total salary for each department and the total salary for all departments , you will have to use a group by Statement statistics The total salary of each department and then the result of the Union with the above to get the final result, but in addition to the writing will be more complicated, but also must scan more than a few times EMP and dept table, Oracle now provides the ROLLUP clause, we can first look at Rollu Result of the P clause:

Sql> Select B.dname, A.job, sum (a.sal)  2 from  emp A, Dept B  3  where A.deptno = B.deptno  4  Grou P by rollup (B.dname, a.job);D name          job       SUM (a.sal)---------------------------------SALES          Clerk            950SALES          MANAGER         2850SALES          salesman        5600SALES                          9400RESEARCH       Clerk           1900RESEARCH       ANALYST         6000RESEARCH       MANAGER         2975RESEARCH                      10875ACCOUNTING     Clerk           1300ACCOUNTING     MANAGER         2450ACCOUNTING     President       5000ACCOUNTING                     8750                              29025

As you can see from the above results, the ROLLUP clause adds a single line to each department and adds a single row to all departments, that is, the total salary for each department and the total salary for all departments.

Typically, rollup is used in conjunction with the group BY statement, which is an extension of the group by statement.

• If the statement is group by rollup (A, b), Oracle will first group by on Fields A and b from right to left, then group by for field A and finally group by for the whole table.
• If the statement is group by rollup (A, B, c), Oracle will first group by on Fields A and B and C from right to left , then group by for fields A and B, and then to field a Grou P by, and finally group by for the whole table.

Below we will demonstrate an example of a rollup three field:

Sql> Select B.dname, A.job, To_char (hiredate, ' yyyy '), sum (SAL) 2 from EMP A, Dept B 3 where A.deptno = B.deptno 4 Group by B.dname, A.job, To_char (hiredate, ' yyyy ') 5 order by 1, 2, 3;dname job To_c SUM (SAL)--------      -----------------------------ACCOUNTING Clerk 1982 1300ACCOUNTING MANAGER 1981 2450ACCOUNTING       President 1981 5000RESEARCH analyst 1981 3000RESEARCH analyst 1987 3000RESEARCH          Clerk 1980 800RESEARCH Clerk 1987 1100RESEARCH MANAGER 1981 2975SALES Clerk 1981 950SALES MANAGER 1981 2850SALES salesman 1981 560011 rows selected .   Sql> Select B.dname, A.job, To_char (hiredate, ' yyyy '), sum (SAL) 2 from EMP A, Dept B 3 where A.deptno = B.deptno 4 GROUP BY rollup (B.dname, A.job, To_char (hiredate, ' yyyy '));D name Job To_c SUM (SAL)-------------------- -----------------SALES Clerk 1981 950SALES Clerk 950SALES MANAGER 1981 2850SALES                               MANAGER 2850SALES salesman 1981 5600SALES salesman 5600SALES       9400RESEARCH Clerk 1980 800RESEARCH Clerk 1987 1100RESEARCH       Clerk 1900RESEARCH Analyst 1981 3000RESEARCH analyst 1987 3000RESEARCH                           ANALYST 6000RESEARCH Manager 1981 2975RESEARCH Manager 2975RESEARCH 10875ACCOUNTING Clerk 1982 1300ACCOUNTING Clerk 1300ACCOUNTING M Anager 1981 2450ACCOUNTING MANAGER 2450ACCOUNTING President 1981 5000ACCOUNTING PRE Sident 5000ACCOUNTING 8750 2902524 rows selected.

1.3 Part Rollup (Partial rollup)

You can use partial rollup when you want to count only part of the field. For example, group by a, rollup (b, c), this statement creates a subtotal of three (2 + 1) levels. Levels (A, B, C), level (A, B), and level (a), respectively.

Sql> Select B.dname, A.job, To_char (hiredate, ' yyyy '), sum (SAL) 2 from EMP A, Dept B 3 where A.deptno = B.deptno 4 GROUP BY B.dname, Rollup (a.job, To_char (hiredate, ' yyyy '));D name Job To_c SUM (SAL)--------------------   -----------------SALES Clerk 1981 950SALES Clerk 950SALES MANAGER             1981 2850SALES MANAGER 2850SALES salesman 1981 5600SALES salesman 5600SALES 9400RESEARCH Clerk 1980 800RESEARCH Clerk 19        1100RESEARCH Clerk 1900RESEARCH Analyst 1981 3000RESEARCH analyst 1987              3000RESEARCH ANALYST 6000RESEARCH Manager 1981 2975RESEARCH Manager                2975RESEARCH 10875ACCOUNTING Clerk 1982 1300ACCOUNTING Clerk 1300ACCOUNTING MANager 1981 2450ACCOUNTING MANAGER 2450ACCOUNTING President 1981 5000ACCOUNTING PRES IDENT 5000ACCOUNTING 875023 rows selected.

As can be seen from the above results, the results of some rollup:

• The normal summary row is generated by group by rather than rollup
• does not produce totals

2. Cube

Cube creates subtotals of various combinations for the specified columns. If the specified number of columns is n, the group by cube creates a subtotal of 2 * n levels. Cube is a more granular grouping of statistical statements than Rollup. First look at the result of the CUBE statement:

2.1 When to use cube

• When there is a similar Cross-tabular report (crosstab) in the requirements
• For statistical summary tables in the Data Warehouse, rollup can simplify the statistical summary table and increase the speed of the Query Statistics summary table

2.2 Cube Example

Sql> Select B.dname, A.job, sum (a.sal)  2 from  emp A, Dept B  3  where A.deptno = B.deptno  4  Grou P by Cube (B.dname, a.job);D name          job       SUM (a.sal)---------------------------------                              29025               Clerk           4150               ANALYST         6000               MANAGER         8275               salesman        5600               President       5000SALES                          9400SALES          Clerk            950SALES          MANAGER         2850SALES          salesman        5600RESEARCH                      10875RESEARCH       Clerk           1900RESEARCH       ANALYST         6000RESEARCH       MANAGER         2975ACCOUNTING                     8750ACCOUNTING     Clerk           1300ACCOUNTING     MANAGER         2450ACCOUNTING     President       500018 rows selected.

As can be seen from the above results, the group by Cube (B.dname, a.job) statement first counts the total salary of all departments, then counts the total salary of each position (a.job), then counts the total salary of each department (B.dname), and finally counts each position in each department. (B.dname, A.job) 's total salary.

• If the statement is group by cube (A, B), Oracle First group by for fields A and B, then group by for Field A, then group by for Field B, and finally group by for the whole table.
• If the statement is group by cube (A, B, c), the fields that are grouped by Oracle are (A, B, C), (A, B), (A, c), (b, C), (a), (b), (c), and finally the total of the entire table

The following shows an example of cube three fields:

Sql> Select B.dname, A.job, To_char (hiredate, ' yyyy '), sum (SAL) 2 from EMP A, Dept B 3 where A.deptno = B.deptno 4 Group BY Cube (B.dname, A.job, To_char (hiredate, ' yyyy '));D name Job To_c SUM (SAL)----------------------                         ---------------29025 1980 800                1981 22825 1982 1300 1987 4100 Clerk        4150 Clerk 1980 Clerk 1981 950 Clerk 1982       1300 Clerk 1987 1100 analyst 6000 analyst 1981       1987 ANALYST 8275 Manager 1981            8275 salesman 5600 salesman 1981 5600 president PRESIDENT 1981 5000SALES 9400SALES 1981 9400SALES Clerk   950SALES Clerk 1981 950SALES manager 2850SALES Manager                           1981 2850SALES salesman 5600SALES salesman 1981 5600RESEARCH 10875RESEARCH 1980 800RESEARCH 1981 5975RESEARCH 19        4100RESEARCH Clerk 1900RESEARCH Clerk 1980 800RESEARCH Clerk 1987       1100RESEARCH analyst 6000RESEARCH Analyst 1981 3000RESEARCH analyst 1987                          3000RESEARCH Manager 2975RESEARCH Manager 1981 2975ACCOUNTING                8750ACCOUNTING 1981 7450ACCOUNTING 1982 1300ACCOUNTING Clerk 1300ACCOUNTING CLERK 1982 1300ACCOUNTING Manager 2450ACCOUNTING Manager 1981 2450ACCOUNTING Presid ENT 5000ACCOUNTING President 1981 500048 rows selected.

2.3 Part cube (partial cube)
some cubes are similar to partial rollup, and placing columns outside of the cube operator can limit the subtotals that generate column combinations. For example, group by A, cube (b, c), this statement will produce a 4 (2 * 2) level of subtotals, respectively, a level (a, B, c), level (A, B), level (A, C), level (a).

Sql> Select B.dname, A.job, To_char (hiredate, ' yyyy '), sum (SAL) 2 from EMP A, Dept B 3 where A.deptno = B.deptno 4 Group by B.dname, Cube (A.job, To_char (hiredate, ' yyyy '));D name Job To_c SUM (SAL)----------------------                 ---------------SALES 9400SALES 1981 9400SALES Clerk 950SALES Clerk 1981 950SALES Manager 2850SALES Manager 19                           Bayi 2850SALES salesman 5600SALES salesman 1981 5600RESEARCH       10875RESEARCH 1980 800RESEARCH 1981 5975RESEARCH 1987       4100RESEARCH Clerk 1900RESEARCH Clerk 1980 800RESEARCH Clerk 1987       1100RESEARCH analyst 6000RESEARCH Analyst 1981 3000RESEARCH analyst 1987 3000RESEARCH MANAGER 2975RESEARCH MANAGER 1981 2975ACCOUNTING 8750ACCOUNTING     1981 7450ACCOUNTING 1982 1300ACCOUNTING Clerk 1300ACCOUNTING Clerk            1982 1300ACCOUNTING Manager 2450ACCOUNTING Manager 1981 2450ACCOUNTING President 5000ACCOUNTING President 1981 500029 rows selected.

Oralce Advanced SQL Rollup and cube