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 > ’85’) SELECT itm.itmn, itpv.vendn FROM itm, itpv WHERE itm.itemn = itpv.itemn AND itm.itemn > ’01’ AND itm.itemn < ’20’ SELECT DISTINCT itm.itmn, pur.vendn FROM itm, itp, pur WHERE itp.ponum = pur.ponum AND itm.itemn = itpv.itemn AND pur.odate > ’85’ AND itm.itemn > ’01’ AND itm.itemn < ’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 Rules:Intersect to Exists SELECT itemn FROM wor WHERE empno = ‘EMPN1279’ INTERSECT SELECT itmn FROM itl WHERE entry_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 (cont.) 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 < 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 < 1-1-80) temp (SUPPNUM,CT) = (select PNUM, count(SHIPDATE) from supply where SHIPDATE < 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.) Parts Supply select PNUM from parts where QOH = ( select count(SHIPDATE) from supply where supply.PNUM = parts.PNUM and SHIPDATE < 1-1-80) Result

12. The Count Bug (cont.) Parts Supply Temp temp (SUPPNUM,CT) = (select PNUM, count(SHIPDATE) from supply where SHIPDATE < 1-1-80) group by PNUM)

13. The Count Bug (cont.) Parts Temp Result select PNUM from parts, temp where parts.QOH = temp.CT and temp.PNUM = parts.PNUM

14. The Count Bug – solutionwith outer joins R S R=+S

15. The Count Bug – solutionwith outer joins temp (SUPPNUM,CT) = (select parts.PNUM, count(SHIPDATE) from parts, supply where SHIPDATE < 1-1-80 and parts.PNUM =+ supply.PNUM group by parts.PNUM) parts.PNUM =+ supply.PNUM (for SHIPDATE < 1-1-80)