Query Rewrite

1
Query Rewrite

• Starburst Model (IBM)

2
DB2 Query Optimizer (Starburst)
Control Flow
Parsing and Semantic Checking
Data Flow
Query Graph Model
Query Rewrite
Plan Optimization
Executable Plan
Compile Time
Run Time
Query Evaluation System
3
Goal of Query Rewrite

• Make queries as declarative as possible
• Poorly expressed queries could force the
  optimizer into choosing suboptimal plans
• Perform natural heuristics
• For example, predicate pushdown

4
Components of Rewrite Engine

• Rewrite rules (more later)
• Rule engine
• control strategies
• sequential (rules are processed sequentially)
• priority (higher priority rules are given a
  chance first)
• statistical (next rule is chosen randomly based
  on a user defined probability distribution
• budget
• to avoid spending too much time on rewrites, the
  processing stops at a consistent state of QGM
  when the budget is exhausted
• Search facility
• browses through QGM providing the context for the
  rules to work on

5
Problem

• How do we choose between competing incompatible
  transformations?
• Optimal solution apply cost analysis and pick
  the transformation leading to a cheaper plan
• Practical solution (why?) generate multiple
  alternatives and send them to plan optimization
  phase (problems?)

6
Rewrite Rules SELECT Merge

• CREATE VIEW itpv AS
• (SELECT DISTINCT itp.itemn, pur.vendn
• FROM itp, pur
• WHERE itp.ponum pur.ponum AND
• pur.odate gt 85)

SELECT itm.itmn, itpv.vendn FROM itm,
itpv WHERE itm.itemn itpv.itemn AND itm.itemn
gt 01 AND itm.itemn lt 20
SELECT DISTINCT itm.itmn, pur.vendn FROM itm,
itp, pur WHERE itp.ponum pur.ponum AND
itm.itemn itpv.itemn AND pur.odate gt 85
AND itm.itemn gt 01 AND itm.itemn lt 20
Speedup 200 times
7
Rewrite Rules Existential Subquery Merge
SELECT FROM itp WHERE itm.itemn
IN ( SELECT itl.itmn FROM itl WHERE itl.wkcen
WK468 AND itl.locan L)
SELECT DISTINCT itp. FROM itp,
itl WHERE itp.itmn itl.itemn AND itl.wkcen
WK468 AND itl.locan L
Speedup 15 times
8
Rewrite RulesIntersect to Exists
SELECT itemn FROM wor WHERE empno
EMPN1279 INTERSECT SELECT itmn FROM itl WHERE en
try_time 9773 AND wkctr WK195)
SELECT DISTINCT itemn FROM wor, itl WHERE empno
EMPN1279 entry_time 9773 AND wkctr
WK195) AND itl.itmn wor.itemn
Speedup 8 times
9
The Count Bug
parts(PNUM,QOH) supply(PNUM,QUAN,SHIPDATE) Query
Find the part numbers of those parts whose
quantities on hand equal the number of shipments
of those parts before 1-1-80. select PNUM from
parts where QOH ( select count(SHIPDATE) from
supply where supply.PNUM parts.PNUM and
SHIPDATE lt 1-1-80)
10
The Count Bug (cont.)
select PNUM from parts where QOH
( select count(SHIPDATE) from
supply where supply.PNUM parts.PNUM and
SHIPDATE lt 1-1-80)
temp (SUPPNUM,CT) (select PNUM,
count(SHIPDATE) from supply where
SHIPDATE lt 1-1-80) group by PNUM)
select PNUM from parts, temp where parts.QOH
temp.CT and temp.PNUM parts.PNUM
11
The Count Bug (cont.)
Supply
Parts
PNUM QOH
3 6
10 1
8 0
PNUM QUAN SHIPDATE
3 4 7-3-79
3 2 10-1-78
10 1 6-8-78
10 2 8-10-81
8 5 5-7-83
select PNUM from parts where QOH
( select count(SHIPDATE) from
supply where supply.PNUM parts.PNUM and
SHIPDATE lt 1-1-80)
Result
PNUM
10
8
12
The Count Bug (cont.)
Supply
Parts
PNUM QOH
3 6
10 1
8 0
PNUM QUAN SHIPDATE
3 4 7-3-79
3 2 10-1-78
10 1 6-8-78
10 2 8-10-81
8 5 5-7-83
Temp
temp (SUPPNUM,CT) (select PNUM,
count(SHIPDATE) from supply where
SHIPDATE lt 1-1-80) group by PNUM)
Suppnum CT
3 2
10 1
13
The Count Bug (cont.)
Parts
Temp
PNUM QOH
3 6
10 1
8 0
SUPPNUM CT
3 2
10 1
Result
select PNUM from parts, temp where parts.QOH
temp.CT and temp.PNUM parts.PNUM
PNUM
10
14
The Count Bug solutionwith outer joins
X
A
B
Y
B
C
E
X Y
A null
B B
null C
null E
R
S
RS
15
The Count Bug solutionwith outer joins
temp (SUPPNUM,CT) (select parts.PNUM,
count(SHIPDATE) from parts,
supply where SHIPDATE lt 1-1-80 and
parts.PNUM supply.PNUM group by parts.PNUM)
parts.PNUM supply.PNUM (for SHIPDATE lt 1-1-80)
Parts.PNUM Parts.QOH Supply.PNUM Supply.QUON Supply.SHIPDATE
3 6 3 4 7-3-79
3 6 3 2 10-1-78
10 1 10 1 6-8-78
8 0 null null null