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 ?
• Isnt it Equality operator ?

7
Venn diagram
B\A
AnB
A\B
(A \ B) ? (B \ A) (A ? B) \ (A n 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.objwhere rownum lt 100000
• create table B asselect obj id, name from
  sys.objwhere rownum lt 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_jo
  in_enabled false
• or
• / use_nl(_at_"SEL74086987" A)   
  use_nl(_at_"SETD8486D66" B)/

14
Symmetric Difference via Aggregation

• select from (  select id, name,     sum(case
  when src1 then 1 else 0 end) cnt1,    
  sum(case when src2 then 1 else 0 end) cnt2  
  from (     select id, name, 1 src from A    
  union all    select id, name, 2 src from B  )
  group by id, name)where cnt1 ltgt cnt2

15
Execution Statistics
16
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
Relational Division
ApplicantSkills
JobApplicants
JobRequirements
x

18
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
Set Union Query

• Given a set of sets,
• e.g 1,3,5,3,4,5,5,6
• Find their union
• SELECT DISTINCT element
• FROM Sets

Sets
21
Set Intersection

• Given a set of sets,
• e.g 1,3,5,3,4,5,5,6
• Find their intersection?

Sets
22
Its Relational Division Query!

• Find Elements which belong to all sets
• compare with
• Find Elements who belong to at least one set

/

23
Implementation (1)

• pName(ApplicantSkills) x JobRequirements

24
Implementation (2)

• Applicants who are not qualified

pName (
pName(ApplicantSkills) x JobRequirements
- ApplicantSkills
)
25
Implementation (3)

• Final Query
• pName (ApplicantSkills) -
• pName ( ApplicantSkills -
• pName(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)

• select distinct 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 doesnt 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 Ø
• select distinct 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