All About Grouping

1
All About Grouping
January 19, 2016

• Rollups, Cubes, Grouping Sets and their inner
  working

• Rob van Wijk

2
Who am I

• Rob van Wijk

• Database application developer

• 14 years with Oracle products

3
All About Grouping

• Topics

• Introduction
• GROUPING SETS
• ROLLUP
• CUBE
• Combining and calculating
• Supporting functions
• Inner working

4
All About Grouping

• Introduction

aog1.sql
January 19, 2016
5
All About Grouping

• GROUPING SETS (1)

• GROUP BY expr1, , exprn
• GROUP BY GROUPING SETS
• ( (expr1, , exprn) )

aog2.sql
January 19, 2016
6
All About Grouping

• GROUPING SETS (2)

• GROUP BY GROUPING SETS
• ( (expr11, , expr1n), , (exprx1, , exprxm) )
• GROUP BY expr11, expr1n
• UNION ALL
• UNION ALL
• GROUP BY exprx1, , exprxm

aog3.sql
January 19, 2016
7
All About Grouping

• ROLLUP (1)

• GROUP BY ROLLUP ( set1, , setn )
• GROUP BY GROUPING SETS
• ( (set1, , setn), (set1, , setn-1), , set1, ()
  )

January 19, 2016
8
All About Grouping

• ROLLUP (2)

• ROLLUP (set1, , setN)
• with N 1
• leads to N1 GROUPING SETS

January 19, 2016
9
All About Grouping

• ROLLUP (3)

• Example
• GROUP BY ROLLUP ( (deptno), (job,mgr), (empno) )
• GROUP BY GROUPING SETS
• ( (deptno,job,mgr,empno)
• , (deptno,job,mgr)
• , (deptno)
• , () )

aog4.sql
January 19, 2016
10
All About Grouping

• CUBE (1)

• GROUP BY CUBE ( set1, , setn )
• GROUP BY GROUPING SETS
• (all possible combinations between () and (set1,
  , setn) )

January 19, 2016
11
All About Grouping

• CUBE (2)

• CUBE (set1, , setN)
• with N 1
• leads to 2N GROUPING SETS

January 19, 2016
12
All About Grouping

• CUBE (3)

Follows Pascals triangle
0 sets X 1 set 2 sets 3 sets 4 sets
January 19, 2016
13
All About Grouping

• CUBE (4)

• Example
• GROUP BY CUBE ( (deptno), (job,mgr), (empno) )
• GROUP BY GROUPING SETS
• ( (deptno,job,mgr,empno)
• , (deptno,job,mgr), (deptno,empno),
  (job,mgr,empno)
• , (deptno), (job,mgr), (empno)
• , () )

aog5.sql
January 19, 2016
14
All About Grouping

• Combining and calculating (1)

• GROUP BY deptno, ROLLUP(empno)
• ?

January 19, 2016
15
All About Grouping

• Combining and calculating (2)

• GROUP BY deptno, ROLLUP(empno)
• GROUP BY GROUPING SETS (deptno)
• , GROUPING SETS ( empno, () )

January 19, 2016
16
All About Grouping

• Combining and calculating (3)

• Cartesian product !
• GROUP BY deptno, ROLLUP(empno)
• GROUP BY GROUPING SETS (deptno)
• , GROUPING SETS ( (empno), () )
• GROUP BY GROUPING SETS
• ( (deptno,empno), (deptno) )

aog6.sql
January 19, 2016
17
All About Grouping

• Combining and calculating (4)

• Question
• How many grouping sets does the clause below
  yield?
• GROUP BY ROLLUP(deptno,job)
• , CUBE(mgr,hiredate)
• Answer 3 4 12

aog7.sql
January 19, 2016
18
All About Grouping

• Supporting functions

• GROUPING
• GROUPING_ID
• GROUP_ID

aog8.sql
January 19, 2016
19
All About Grouping

• Inner working

• SORT GROUP BY
• versus
• HASH GROUP BY

January 19, 2016
20
All About Grouping

• Inner working ROLLUP (deptno,empno)

incoming set
SORT GROUP BY
grouping set ( (deptno) )
10 NULL 8750
20 NULL 10875
30 NULL 9400

SORT GROUP BY
grouping set ( () )

NULL NULL 29025
SORT GROUP BY
aog9.sql
January 19, 2016
21
All About Grouping

• Inner working CUBE(deptno,job)

14 rows
incoming set
grouping set (deptno,job)
SORT GROUP BY
9 rows
36 rows
GENERATE CUBE
18 rows
SORT GROUP BY
aog10.sql
January 19, 2016
22
All About Grouping

• Inner working GROUPING SETS (1)

LOAD AS SELECT (into outputtable) HASH GROUP
BY TABLE ACCESS FULL (input table)
iterate as much times as there are grouping sets
aog11.sql
January 19, 2016
23
All About Grouping

• Inner working GROUPING SETS (2)

• Optimize towards a ROLLUP or CUBE execution,
• if possible?

aog12.sql
January 19, 2016
24
All About Grouping

• Questions?

January 19, 2016
25
All About Grouping
Thanks for your attention! Email
rwijk72_at_gmail.com Blog http//rwijk.blogspot.com
January 19, 2016