SQL Design Patterns

1. SQL Design Patterns Advanced SQL programming idioms

2. Genesis • C++ world Advanced C++ Programming Styles and Idioms, by James O. Coplien Design Patterns: Elements of Reusable Object-Oriented Software by Erich Gamma et al • SQL SQL for Smarties by Joe Celko SQL Cookbook by Anthony Molinaro The Art of SQL by Stephane Faroult, Peter Robson

3. What is a SQL Pattern? • A common design vocabulary • A documentation and learning aid • An adjunct to existing design methods • A target for refactoring • Large range of granularity -- from very general design principles to language-specific idioms

4. List of Patterns • Counting • Conditional summation • Integer generator • String/Collection decomposition • List Aggregate • Enumerating pairs • Enumerating sets • Interval coalesce

5. Discrete interval sampling • User-defined aggregate • Pivot • Symmetric difference • Histogram • Skyline query • Relational division • Outer union • Complex constraint • Nested intervals • Transitive closure • Hierarchical total

6. Symmetric Difference • A = B ? • Isn’t it Equality operator ?

7. Venn diagram B\A A∩B A\B (A \ B) ∪ (B \ A) (A ∪ B) \ (A ∩ B)

8. SQL Query ( select * from A minus select * from B ) union all ( select * from B minus select * from A )

9. Test create table A asselect obj# id, name from sys.obj$where rownum < 100000; create table B asselect obj# id, name from sys.obj$where rownum < 100010;

10. Execution Statistics

11. Anti Join Transformation convert_set_to_join = true: select * from A where (col1,col2,…) not in (select col1,col2,… from B) union all select * from B where (col1,col2,…) not in (select col1,col2,… from A)

12. Execution Statistics

13. Optimization continued… • CREATE INDEX A_id_name ON A(id, name); CREATE INDEX B_id_name ON B(id, name); • _hash_join_enabled = false_optimizer_sortmerge_join_enabled = false or /*+ use_nl(@"SEL$74086987" A) use_nl(@"SET$D8486D66" B)*/

14. Symmetric Difference via Aggregation select * from (select id, name, sum(casewhen src=1 then 1 else 0 end) cnt1, sum(case when src=2 then 1 else 0 end) cnt2 from ( select id, name, 1 src from A union allselect id, name, 2 src from B ) group by id, name)where cnt1 <> cnt2

15. Execution Statistics

16. || || || orahash orahash orahash 592731 267629 334382 Equality checking via Aggregation • 1.Is there any difference? (Boolean). • 2. What are the rows that one table contains, and the other doesn't? || orahash 512259 + + + = 1523431

17. Name Language Language Steve SQL SQL Name Java Pete Java Steve Kate SQL Pete Kate Steve Java Pete SQL Kate Java Relational Division ApplicantSkills JobApplicants JobRequirements x =

18. Name Name Language Language Steve Kate SQL SQL Java Pete Java Kate SQL ApplicantSkills Kate Java JobRequirements / ? = Dividend, Divisor and Quotient Remainder

19. Is it a common Pattern? • Not a basic operator in RA or SQL • Informally: “Find job applicants who meet all job requirements” compare with: “Find job applicants who meet at least one job requirement”

20. ID ELEMENT 1 1 1 3 1 5 2 3 2 4 2 5 3 5 3 6 Set Union Query Given a set of sets, e.g {{1,3,5},{3,4,5},{5,6}} Find their union: SELECT DISTINCTelement FROM Sets Sets

21. ID ELEMENT 1 1 1 3 1 5 2 3 2 4 2 5 3 5 3 6 Set Intersection Given a set of sets, e.g {{1,3,5},{3,4,5},{5,6}} Find their intersection? Sets

22. ID ELEMENT ID ELEMENT 1 5 1 1 1 2 3 3 1 5 2 3 2 4 2 5 3 5 3 6 It’s Relational Division Query! “Find Elements which belong to all sets” compare with: “Find Elements who belong to at least one set” / =

23. Name Language Steve SQL Pete Java Kate SQL Steve Java Pete SQL Kate Java Implementation (1) πName(ApplicantSkills)x JobRequirements

24. Implementation (2) Applicants who are not qualified: πName ( πName(ApplicantSkills)x JobRequirements - ApplicantSkills )

25. Implementation (3) Final Query: πName (ApplicantSkills)- πName ( ApplicantSkills - πName(ApplicantSkills)x JobRequirements )

26. Implementation in SQL (1) select distinct Name from ApplicantSkills minus select Name from ( select Name, Language from ( select Name from ApplicantSkills ), ( select Language from JobRequirements ) minus select Name, Language from ApplicantSkills )

27. Implementation in SQL (2) selectdistinct Name from ApplicantSkills i where not exists ( select * from JobRequirements ii where not exists ( select * from ApplicantSkills iii where iii.Language = ii.Language and iii.Name = i.Name ) )

28. Implementation in SQL (3) “Name the applicants such that for all job requirements there exists a corresponding entry in the applicant skills”  “Name the applicants such that there is no job requirement such that there doesn’t exists a corresponding entry in the applicant skills” “Name the applicants for which the set of all job skills is a subset of their skills”

29. Implementation in SQL (4) select distinct Name from ApplicantSkills i where (select Language from JobRequirements ii where ii.Name = i.Name) in (select Language from ApplicantSkills)

30. Implementation in SQL (5) A ⊆ BA \ B = ∅ selectdistinct Name from ApplicantSkills i where not exists ( select Language from ApplicantSkills minus select Language from JobRequirements ii where ii.Name = i.Name )

31. Implementation in SQL (6) select Name from ApplicantSkills s, JobRequirements r where s.Language = r.Language group by Name having count(*) = (select count(*) from JobRequirements)

32. Book