1 / 15

Query Rewrite

Query Rewrite. Starburst Model (IBM). 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. Goal of Query Rewrite.

gloverk
Download Presentation

Query Rewrite

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 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)

More Related