Oracle University Live Virtual Seminar SQL Masterclass Rob

Oracle University Live Virtual Seminar SQL Masterclass Rob van Wijk • 2011

About me • • Work with Oracle and SQL since 1995 From: Utrecht, Netherlands • Blog: • Forums: •

Agenda Part One: Do More With SQL Analytic Functions 15 -minute break Part Two: SQL Model Clause one-hour break Part Three: Recursive Subquery Factoring 15 -minute break Part Four: Grouping & Aggregating Frequently Occuring SQL Problems

Part 1 a: Do More With SQL

Goals • As practical as possible • As less regurgitating of documentation as possible • Lots of example scripts • Recognizable problems • Do as much as possible in SQL and avoid shipping records for processing to PL/SQL or even Java at a middle tier.

If you want to build a ship, don't drum up the men to gather wood, divide the work and give orders. Instead, teach them to yearn for the vast and endless sea. – Antoine de Saint Exupéry

One SQL engine versus …

… two engines. context swtiches procedural engine SQL engine dmws 1. sql

You risk wrong results because of different start times of queries with default READ COMMITTED isolation level dmws 2. sql

Using SELECT statements in your DML • INSERT INTO … SELECT … • DELETE … WHERE rowid IN ( SELECT … ) • The trouble with UPDATE statements • Use Merge uj 1. sql • Updateable Join Views uj 2. sql

Part 1 b: Analytic Functions

Analytic Functions: Topics • Introduction • Mind set • Evaluation order • Main syntax • Examples • Window clause

Analytic Functions: Introduction Of every employee please show me: • His name • The department he’s working in • His salary • The cumulative salary per department • Percentage of salary within the company where employees are sorted by department and salary af 1 a. sql af 1 b. sql af 1 c. sql

Analytic Functions: Introduction • Since 8. 1. 6 Enterprise Edition • Look like well known aggregate functions like SUM, COUNT and AVG • … but they don’t aggregate • Prevents self joins • Have been extended with new functions and new options in more recent versions af 2. sql

Analytic Functions: Mind set Don’t think “rows” … EMPNO -----7782 7839 7934 7876 ENAME -----CLARK KING MILLER ADAMS JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------MANAGER 7839 09 -06 -81 2450 10 PRESIDENT 17 -11 -81 5000 10 CLERK 7782 23 -01 -82 1300 10 CLERK 7788 23 -05 -87 1100 20 7902 FORD ANALYST 7566 03 -12 -81 3000 20 7566 7788 7369 7499 7698 7900 7654 7844 7521 MANAGER ANALYST CLERK SALESMAN MANAGER CLERK SALESMAN 7839 7566 7902 7698 7839 7698 2975 3000 800 1600 2850 950 1250 1500 1250 20 20 20 30 30 30 JONES SCOTT SMITH ALLEN BLAKE JAMES MARTIN TURNER WARD 02 -04 -81 19 -04 -87 17 -12 -80 20 -02 -81 01 -05 -81 03 -12 -81 28 -09 -81 08 -09 -81 22 -02 -81 300 1400 0 500

Analytic Functions: Mind set … but think “sets” EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----------7782 7839 7934 7876 7902 7566 7788 7369 7499 7698 7900 7654 7844 7521 CLARK KING MILLER ADAMS FORD JONES SCOTT SMITH ALLEN BLAKE JAMES MARTIN TURNER WARD MANAGER PRESIDENT CLERK ANALYST MANAGER ANALYST CLERK SALESMAN MANAGER CLERK SALESMAN 7839 09 -06 -81 17 -11 -81 7782 23 -01 -82 7788 23 -05 -87 7566 03 -12 -81 7839 02 -04 -81 7566 19 -04 -87 7902 17 -12 -80 7698 20 -02 -81 7839 01 -05 -81 7698 03 -12 -81 7698 28 -09 -81 7698 08 -09 -81 7698 22 -02 -81 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----------7782 CLARK 7839 KING 7934 MILLER MANAGER PRESIDENT CLERK 7839 09 -06 -81 17 -11 -81 7782 23 -01 -82 2450 5000 1300 10 10 10 7876 7902 7566 7788 7369 ADAMS FORD JONES SCOTT SMITH CLERK ANALYST MANAGER ANALYST CLERK 7788 7566 7839 7566 7902 23 -05 -87 03 -12 -81 02 -04 -81 19 -04 -87 17 -12 -80 1100 3000 2975 3000 800 20 20 20 7499 7698 7900 7654 7844 7521 ALLEN BLAKE JAMES MARTIN TURNER WARD SALESMAN MANAGER CLERK SALESMAN 7698 7839 7698 20 -02 -81 01 -05 -81 03 -12 -81 28 -09 -81 08 -09 -81 22 -02 -81 1600 2850 950 1250 1500 1250 300 1400 0 500 30 30 30 2450 5000 1300 1100 3000 2975 3000 800 1600 2850 950 1250 1500 1250 300 1400 0 500 10 10 10 20 20 20 30 30 30

Analytic Functions: Evaluation order • Last • Even after evaluating HAVING clause • And after ROWNUM has been assigned • But before ORDER BY clause • Filtering on outcome of analytic function: nest the query using an inline view or use subquery factoring af 3 a. sql af 3 b. sql

Analytic Functions: Main syntax <function> (<argument>, …) OVER (<partition clause> <order by clause> <window clause> )

Analytic Functions: The functions LAG FIRST / LAST PERCENT_RANK LEAD COUNT PERCENTILE_DISC FIRST_VALUE SUM PERCENTILE_CONT LAST_VALUE MAX CORR NTH_VALUE MIN COVAR_POP RANK AVG VARIANCE DENSE_RANK NTILE VAR_x (2 times) RATIO_TO_REPORT CUME_DIST STDDEV_x (3 times) ROW_NUMBER LISTAGG REGR_x (9 times)

Analytic Functions: Partition clause PARTITION BY <expression> [, <expression>]* to let the analytic function operate on a subset of the rows with the same values for the partition by expression values. af 4. sql

Analytic Functions: Order By clause ORDER BY <expression> [ASC|DESC] [NULLS FIRST|NULLS LAST], … Its presence changes the default window of an analytic function from the total set to a running total. af 5. sql

Analytic Functions: Example 1 Top N queries What do I mean exactly with: “Show me the top 3 earning employees per department” • RANK • DENSE_RANK • ROW_NUMBER af 6. sql

Analytic Functions: Example 2 1. David Zabriskie (USA) 2. Ivan Basso (ITA) 3. Paolo Savoldelli (ITA) 4. Marzio Bruseghin (ITA) 5. Serguei Gonchar (UKR) 6. Vladimir Karpets (RUS) 7. Markus Fothen (GER) 8. Thomas Dekker (NLD) 9. Jan Hruska (CZE) 10. Danilo di Luca (ITA) 0. 58: 31 + 0: 17 + 0: 44 + 0: 48 z. t. + 1: 07 + 1: 15 + 1: 23 + 1: 34 z. t. af 7. sql

Analytic Functions: Example 3 • Requirement: non-overlapping & consecutive periods • Columns Startdate and maybe Enddate • Optimize to retrieve current period • Options: 1) No Enddate column and use correlated subquery 2) Enddate column and database trigger code to check requirement 3) No Enddate column and use analytic function af 8. sql

Analytic Functions: Example 4 • Bills can be of type “Prepayment” or “Settlement” • Bill lines have an amount. • Each customer pays a prepayment each month. The bill contains one bill line with the amount. • Each customer receives once a year a settlement bill. • How to calculate the previous prepayment amount? This is the amount before the last settlement bill. af 9. sql

Analytic Functions: Example 5 TIME QUANTITY ----------12: 22: 01 100 12: 22: 03 200 12: 22: 04 300 12: 22: 06 200 12: 22: 45 100 12: 22: 46 200 12: 23: 12 100 12: 23: 12 200 MIN(TIME) ----12: 22: 01 12: 22: 45 12: 23: 12 MAX(TIME) QUANTITY ----------12: 22: 06 800 12: 22: 46 300 12: 23: 12 300 af 10. sql

Analytic Functions: Window clause • Total set: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING • Anchored set / running aggregate: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW • ROW / RANGE af 11. sql af 12. sql af 13. sql

Part 2: SQL Model Clause

SQL Model Clause: Topics • Introduction • Syntax • Examples • Performance • Alternatives • Conclusion

SQL Model Clause: Introduction • Treat data as multidimensional arrays • Complex calculations across rows • Syntax which resembles logic programming (Prolog) • Can prevent exporting data to external applications like Excel/Numbers • No more several copies of data on several PC’s anymore

SQL Model Clause: Syntax (1) • Divide columns in three groups: PARTITION, DIMENSION and MEASURES • Every partition is a separate array • Dimensions identify a cell in every partition • Measures are the columns you want to (re-)calculate • The rules tell you how the data is to be manipulated

SQL Model Clause: Examples • A model clause that does nothing mc 1. sql • Adding an extra row to the result set mc 2. sql • RETURN UPDATED ROWS mc 3. sql • The difference between MEASURES and PARTITION mc 4. sql

SQL Model Clause: Example from the doc mc 5. sql

SQL Model Clause: More examples • ANY mc 6. sql • CV() mc 7. sql • FOR mc 8. sql • Iterating mc 9. sql • Reference models mc 10. sql • Difference between NULL and NAV mc 11. sql • IS PRESENT, PRESENTV and PRESENTNNV mc 12. sql

SQL Model Clause: Complete syntax MODEL [<global reference options>] [<reference models>] [MAIN <main-name>] [PARTITION BY (<cols>)] DIMENSION BY (<cols>) MEASURES (<cols>) [<reference options>] [RULES] <rule options> (<rule>, . . , <rule>) <global reference options> : : = <reference options> <ret-opt> : : = RETURN {ALL|UPDATED} ROWS <reference options> : : = [IGNORE NAV | [KEEP NAV] [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE] <rule options> : : = [UPDATE | UPSERT ALL] [AUTOMATIC ORDER | SEQUENTIAL ORDER] [ITERATE (<number>) [UNTIL <condition>]] <reference models> : : = REFERENCE ON <ref-name> ON (<query>) DIMENSION BY (<cols>) MEASURES (<cols>) <reference options>

SQL Model Clause: Examples • Financial spreadsheet mc 13. sql • Fibonacci mc 14. sql • OTN-question mc 15. sql • Interest and rates mc 16. sql

SQL Model Clause: Performance • Internal hash-tables in PGA • Sequential Order SQL MODEL ORDERED [FAST] • Automatic Order SQL MODEL [A]CYCLIC • FAST left side cell references are single cell references and aggregates at right side -if any- are simple arithmetic non-distinct aggregates, like SUM, COUNT, AVG and so on. mc 17. sql

SQL Model Clause: Advanced examples • Calculating die probabilities mc 18. sql • Exponential Moving Average X = (K * (C - P)) + P Where: X = Current EMA (i. e. EMA to be calculated) C = Current original data value K = Smoothing Constant P = Previous EMA • Sudoku solver mc 19. sql mc 20. sql

Part 3: Recursive Subquery Factoring

Recursive Subquery Factoring: Topics • Subquery Factoring • Concepts • Recursive Examples • Simulating Connect By • Performance • More recursive examples

Subquery Factoring • Since version 9 • Let’s you assign a name to a subquery block • Modular Programming in SQL • Also known as “WITH clause” or “Common Table Expressions” • Second to last factored subquery: comma instead of rsf 1. sql “WITH” • /*+ MATERIALIZE */ and /*+ INLINE */ rsf 2. sql • Must use each factored subquery? rsf 3. sql

Recursive Subquery Factoring: Concepts • Since version 11. 2 • Let’s you query hierarchical data • More powerful than CONNECT BY • Anchor member UNION ALL recursive member rsf 4. sql • Recursive member cannot contain: DISTINCT, Model clause, aggregate functions and analytic functions • SEARCH DEPTH / BREADTH FIRST rsf 5. sql • CYCLE rsf 6. sql

Recursive Subquery Factoring: Examples • Fibonacci • fib(0) = 0 • fib(1) = 1 • fib(n+2) = fib(n+1) + fib(n) • Interest and rates rsf 7. sql rsf 8. sql

Simulating Connect By • LEVEL rsf 9. sql • SYS_CONNECT_BY_PATH rsf 10. sql • CONNECT_BY_ROOT rsf 11. sql • CONNECT_BY_ISCYCLE rsf 12. sql • CONNECT_BY_ISLEAF rsf 13. sql

Recursive Subquery Factoring: Performance • /*+ CONNECT_BY_FILTERING */ • /*+ NO_CONNECT_BY_FILTERING */ rsf 14. sql

More Recursive Examples • Calculating die probabilities rsf 15. sql • Exponential Moving Average X = (K * (C - P)) + P Where: X = Current EMA (i. e. EMA to be calculated) C = Current original data value K = Smoothing Constant P = Previous EMA • Sudoku solver rsf 16. sql rsf 17. sql

Part 4 a: Grouping & Aggregating

aog 1. sql

Grouping & Aggregating: Topics • Introduction • GROUPING SETS • ROLLUP • CUBE • Combining and calculating • Supporting functions • Inner workings • MIN/MAX … KEEP … (DENSE_RANK FIRST/LAST … )

Grouping & Aggregating: Grouping Sets (1) GROUP BY expr 1, …, exprn ≡ GROUP BY GROUPING SETS ( (expr 1, …, exprn) ) aog 2. sql

Grouping & Aggregating: Grouping Sets (2) GROUP BY GROUPING SETS ( (expr 11, …, expr 1 n), …, (exprx 1, …, exprxm) ) ≡ GROUP BY expr 11, … expr 1 n UNION ALL … UNION ALL GROUP BY exprx 1, …, exprxm aog 3. sql

Grouping & Aggregating: ROLLUP (1) GROUP BY ROLLUP ( set 1, …, setn ) ≡ GROUP BY GROUPING SETS ( (set 1, …, setn), (set 1, …, setn-1), …, set 1, () )

Grouping & Aggregating: ROLLUP (2) ROLLUP (set 1, …, set. N) with N ≥ 1 leads to N+1 GROUPING SETS

Grouping & Aggregating: ROLLUP (3) Example: GROUP BY ROLLUP ( (deptno), (job, mgr), (empno) ) ≡ GROUP BY GROUPING SETS ( (deptno, job, mgr, empno) , (deptno, job, mgr) , (deptno) , () ) aog 4. sql

Grouping & Aggregating: CUBE (1) GROUP BY CUBE ( set 1, …, setn ) ≡ GROUP BY GROUPING SETS (all possible combinations between () and (set 1, …, setn) )

Grouping & Aggregating: CUBE (2) CUBE (set 1, …, set. N) with N ≥ 1 leads to 2 N GROUPING SETS

Grouping & Aggregating: CUBE (3) Follows Pascal’s triangle 0 sets X 1 set 2 sets 3 sets 4 sets

Grouping & Aggregating: 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) , () ) aog 5. sql

Grouping & Aggregating: Calculating (1) GROUP BY deptno, ROLLUP(empno) ?

Grouping & Aggregating: Calculating (2) GROUP BY deptno, ROLLUP(empno) ≡ GROUP BY GROUPING SETS (deptno) , GROUPING SETS ( empno, () )

Grouping & Aggregating: Calculating (3) Cartesian product ! GROUP BY deptno, ROLLUP(empno) ≡ GROUP BY GROUPING SETS (deptno) , GROUPING SETS ( (empno), () ) ≡ GROUP BY GROUPING SETS ( (deptno, empno), (deptno) ) aog 6. sql

Grouping & Aggregating: Calculating (4) Question: How many grouping sets does the clause below yield? GROUP BY ROLLUP(deptno, job) , CUBE(mgr, hiredate) aog 7. sql

Grouping & Aggregating: Functions GROUPING_ID GROUP_ID aog 8. sql

Grouping & Aggregating: Inner working (1) SORT GROUP BY Versus HASH GROUP BY

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

Grouping & Aggregating: Inner working (3) CUBE (DEPTNO, JOB) 14 rows incoming set 9 rows SORT GROUP BY (deptno, job) deptno not null & job not null deptno not null & job null deptno null & job not null deptno null & job null GENERATE CUBE 36 rows 18 rows SORT GROUP BY (deptno, job) aog 10. sql

Grouping & Aggregating: Inner working (4) LOAD AS SELECT (into input table) TABLE ACCESS FULL (EMP) temporary input table SYS_TEMP_. . . LOAD AS SELECT (into outputtable) HASH GROUP BY TABLE ACCESS FULL (input table) iterate as much times as there are grouping sets temporary output table SYS_TEMP_. . . TEMP TABLE TRANSFORMATION VIEW TABLE ACCESS FULL (output table) aog 11. sql

Grouping & Aggregating: Inner working (5) Optimize towards a ROLLUP or CUBE execution, if possible? aog 12. sql

Grouping & Aggregating: Agg. Functions (1) • COUNT • SUM • AVG • MAX • MIN • STDDEV • VARIANCE • LISTAGG aog 13. sql

Grouping & Aggregating: Agg. Functions (2) • MAX(…) KEEP (DENSE_RANK FIRST ORDER BY …) • MAX(…) KEEP (DENSE_RANK LAST ORDER BY …) • MIN(…) KEEP (DENSE_RANK FIRST ORDER BY …) • MIN(…) KEEP (DENSE_RANK LAST ORDER BY …) aog 14. sql

Frequently Occuring Problems • Row / Number Generation • Interval Based Row Generation • Splitting Comma Separated Strings • String Aggregation • Pivoting • Unpivoting • Tabibitosan

Part 4 b: Frequently Occuring Problems

Row / Number Generation fop 1. sql

Interval Based Row Generation fop 2. sql

Splitting Comma Separated Strings → fop 3. sql

String Aggregation → fop 4. sql

Pivoting fop 5. sql

Unpivoting fop 6. sql

Tabibitosan fop 7. sql

Thank you for your attention 81 • Title of presentation