Oracle rollup and cube Statement Analysis

Oracle rollup and cube Statement Analysis rollup is an extension of group by, which performs subtotal and total, while cube contains rollup, which is a more fine-grained subtotal and total. When there is only one field, rollup and cube are the same. You can use grouping to test rollup and cube: 0; 1. If you do not view it, [view indicates that the column is displayed, if you do not view it, it indicates that it is not displayed] ① rollup www.2cto.com (I) do not view anything. (ii) from left to right, first view 1st │ then view the first two shards and then view the first three ② cubes: the columns specified after rollup are separated by commas (,). The calculation result of rollup is related to the sequence of the columns specified after rollup, because the rollup group has directionality. If n columns are specified, n + 1 grouping method is available. You can change the column order to meet different business needs. Be sure to keep in mind the impact of column order on results! Example: [SQL] 20:32:51 scott @ ORCL (^ ω ^) select. dname, B. job, sum (B. sal) sum_sal, grouping (. dname), grouping (B. job) www.2cto.com 20:33:06 2 from dept a, emp B 20:33:06 3 where. deptno = B. deptno 20:33:06 4 group by rollup (. dname, B. job) 20:33:08 5/dname job SUM_SAL GROUPING (. DNAME) GROUPING (B. JOB) ---------- ----------------- --------------- sales clerk 950 0 0 sales manager 2850 0 0 sales salesman 5600 0 0 SALES 9400 0 1 research clerk 1200 0 0 research analyst 7000 0 0 research manager 2975 0 RESEARCH 11175 0 0 1 accounting clerk 1300 0 0 accounting manager 2450 0 0 ACCOUNTING PRESIDENT 5000 0 0 ACCOUNTING 8750 0 1 29325 1 1 1 Selected 13 rows. Www.2cto.com explanation: Do not read anything (); from left to right, first look at the first (), you can remove columns that do not need to be subtotal or aggregate from rollup, the subtotal is left in rollup. For example, [SQL] 20:48:37 scott @ ORCL (^ ω ^) select. dname, B. job, sum (B. sal) sum_sal, grouping (. dname), grouping (B. job) 20:57:43 2 from dept a, emp B 20:57:43 3 where. deptno = B. deptno 20:57:43 4 group by. dname, rollup (B. job) 20:57:45/www.2cto.com dname job SUM_SAL GROUPING (. DNAME) GROUPING (B. JOB) ---------------------------- ------------------ ---------- ----------------- --------------- SALES cler95 0 0 0 sales manager 2850 0 0 sales salesman 5600 0 0 SALES 9400 0 1 research clerk 1200 0 0 research analyst 7000 0 0 research manager 2975 0 0 RESEARCH 11175 0 1 accounting clerk 1300 0 0 accounting manager 2450 0 0 accounting president 5000 0 0 ACCOUNTING 8750 0 1 Selected 12 rows. Different from rollup, cube calculation results and sequence are irrelevant. If n columns exist, the grouping method has the Npower of 2.
[SQL] 20:57:46 scott @ ORCL (^ ω ^) select. dname, B. job, sum (B. sal) sum_sal, grouping (. dname), grouping (B. job) 21:27:03 2 from dept a, emp B 21:27:03 3 where. deptno = B. deptno 21:27:03 4 group by cube (. dname, B. job) 21:27:04/www.2cto.com dname job SUM_SAL GROUPING (. DNAME) GROUPING (B. JOB) ---------------------------- ------------------ ---------- ----------------- --------------- 29325 1 1 CLERK 3450 1 0 ANALYST 7000 1 0 MANAGER 8275 1 0 SALESMAN 5600 1 0 PRESIDENT 5000 1 0 SALES 9400 0 1 sales clerk 950 0 0 sales manager 2850 0 0 sales salesman 5600 0 0 RESEARCH 11175 0 1 research clerk 1200 0 0 research analyst 7000 0 research manager 2975 0 0 ACCOUNTING 8750 0 1 accounting clerk 1300 0 0 accounting manager 2450 0 0 accounting president 5000 0 0 has selected 18 rows. Www.2cto.com Note: all possible groups of the cube are taken over the result of rollup.
You can remove the aggregate and some unwanted subtotal, which are implemented through some cubes. Some cubes are more useful than some rollup. [SQL] 21:27:06 scott @ ORCL (^ ω ^) select. dname, B. job, sum (B. sal) sum_sal, grouping (. dname), grouping (B. job) 21:32:20 2 from dept a, emp B 21:32:20 3 where. deptno = B. deptno 21:32:20 4 group by. dname, cube (B. job) 21:32:21 5/dname job SUM_SAL GROUPING (. DNAME) GROUPING (B. JOB) ---------------------------- ------------------ ---------- ----------------- --------------- SALES 9400 0 1 sales clerk 950 0 0 sales manager 2850 0 0 sales salesman 5600 0 RESEARCH 11175 0 1 www.2cto.com research clerk 1200 0 0 research analyst 7000 0 0 research manager 2975 0 0 0 ACCOUNTING 8750 0 1 accounting clerk 1300 0 0 accounting manager 2450 0 0 accounting president 5000 0 0 selected 12 rows.