1 / 25

All About Grouping

All About Grouping. October 29, 2014. Rollups, Cubes, Grouping Sets and their inner working. Rob van Wijk. Utrecht. Who am I. Rob van Wijk. Database application developer. 14 years with Oracle products. All About Grouping. Introduction GROUPING SETS ROLLUP CUBE

sarah-owens
Download Presentation

All About Grouping

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. All About Grouping October 29, 2014 Rollups, Cubes, Grouping Sets and their inner working Rob van Wijk

  2. Utrecht Who am I • Rob van Wijk • Database application developer • 14 years with Oracle products

  3. All About Grouping • Introduction • GROUPING SETS • ROLLUP • CUBE • Combining and calculating • Supporting functions • Inner working • Topics

  4. All About Grouping Introduction aog1.sql October 29, 2014

  5. All About Grouping GROUP BY expr1, …, exprn ≡ GROUP BY GROUPING SETS ( (expr1, …, exprn) ) GROUPING SETS (1) aog2.sql October 29, 2014

  6. All About Grouping GROUP BY GROUPING SETS ( (expr11, …, expr1n), …, (exprx1, …, exprxm) ) ≡ GROUP BY expr11, … expr1n UNION ALL … UNION ALL GROUP BY exprx1, …, exprxm GROUPING SETS (2) aog3.sql October 29, 2014

  7. All About Grouping GROUP BY ROLLUP ( set1, …, setn ) ≡ GROUP BY GROUPING SETS ( (set1, …, setn), (set1, …, setn-1), …, set1, () ) ROLLUP (1) October 29, 2014

  8. All About Grouping ROLLUP (set1, …, setN) with N ≥ 1 leads to N+1 GROUPING SETS ROLLUP (2) October 29, 2014

  9. All About Grouping Example: GROUP BY ROLLUP ( (deptno), (job,mgr), (empno) ) ≡ GROUP BY GROUPING SETS ( (deptno,job,mgr,empno) , (deptno,job,mgr) , (deptno) , () ) ROLLUP (3) aog4.sql October 29, 2014

  10. All About Grouping GROUP BY CUBE ( set1, …, setn ) ≡ GROUP BY GROUPING SETS (all possible combinations between () and (set1, …, setn) ) CUBE (1) October 29, 2014

  11. All About Grouping CUBE (set1, …, setN) with N ≥ 1 leads to 2N GROUPING SETS CUBE (2) October 29, 2014

  12. All About Grouping CUBE (3) Follows Pascal’s triangle 0 setsX 1 set 2 sets 3 sets 4 sets October 29, 2014

  13. All About Grouping 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) , () ) CUBE (4) aog5.sql October 29, 2014

  14. All About Grouping GROUP BY deptno, ROLLUP(empno) ? Combining and calculating (1) October 29, 2014

  15. All About Grouping GROUP BY deptno, ROLLUP(empno) ≡ GROUP BY GROUPING SETS (deptno) , GROUPING SETS ( empno, () ) Combining and calculating (2) October 29, 2014

  16. All About Grouping Cartesian product ! GROUP BY deptno, ROLLUP(empno) ≡ GROUP BY GROUPING SETS (deptno) , GROUPING SETS ( (empno), () ) ≡ GROUP BY GROUPING SETS ( (deptno,empno), (deptno) ) Combining and calculating (3) aog6.sql October 29, 2014

  17. All About Grouping Question: How many grouping sets does the clause below yield? GROUP BY ROLLUP(deptno,job) , CUBE(mgr,hiredate) Answer: 3 * 4 = 12 Combining and calculating (4) aog7.sql October 29, 2014

  18. All About Grouping GROUPING GROUPING_ID GROUP_ID Supporting functions aog8.sql October 29, 2014

  19. All About Grouping SORT GROUP BY versus HASH GROUP BY Inner working October 29, 2014

  20. All About Grouping 30 7900 950 30 7844 1500 30 7654 1250 30 7698 2850 30 7521 1250 20 7876 1100 30 7499 1600 20 7788 3000 20 7902 3000 20 7566 2975 20 7369 800 10 7839 5000 10 7934 1300 10 7782 2450 grouping set ( (deptno,empno) ) incoming set Inner working: ROLLUP (deptno,empno) SORT GROUP BY grouping set ( (deptno) ) 30 NULL 9400 10 NULL 8750 20 NULL 10875 + SORT GROUP BY grouping set ( () ) + NULL NULL 29025 SORT GROUP BY aog9.sql October 29, 2014

  21. All About Grouping Inner working: CUBE(deptno,job) 14 rows incoming set grouping set (deptno,job) 9 rows SORT GROUP BY 36 rows GENERATE CUBE 18 rows SORT GROUP BY aog10.sql October 29, 2014

  22. All About Grouping temporary input table SYS_TEMP_... LOAD AS SELECT (into input table) TABLE ACCESS FULL (EMP) TEMP TABLE TRANSFORMATION VIEW TABLE ACCESS FULL (output table) temporary output table SYS_TEMP_... 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 October 29, 2014

  23. All About Grouping Optimize towards a ROLLUP or CUBE execution, if possible? Inner working: GROUPING SETS (2) aog12.sql October 29, 2014

  24. All About Grouping Questions? October 29, 2014

  25. All About Grouping Thanks for your attention! Email: rwijk72@gmail.com Blog: http://rwijk.blogspot.com October 29, 2014

More Related