1 / 6

Extensible/Rule Based Query Rewrite Optimization in Starburst

?. Hamid Pirahesh. Joseph M. Hellerstein. Waqar Hasan. Extensible/Rule Based Query Rewrite Optimization in Starburst. SIGMOD Conference 1992: p39-48. Daniel Ballinger:

flanagand
Télécharger la présentation

Extensible/Rule Based Query Rewrite Optimization in Starburst

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. ? Hamid Pirahesh Joseph M. Hellerstein Waqar Hasan Extensible/Rule Based Query Rewrite Optimization in Starburst SIGMOD Conference 1992: p39-48 Daniel Ballinger

  2. Daniel Ballinger: Traditional database systems typically just perform a single phase of query optimization to choose access methods and join orders/methods to provide an efficient plan. (plan optimization) The Problems Being Addressed • SQL is not a pure declarative query language as it has imperative features. • Complex queries can contain subqueries and views. • These naturally divide a query into nested blocks and • can create evaluation path expressions. • Traditional DBMS only perform plan optimisation on a single query block at a time and perform no cross-block optimisation. • The result: query optimisers are often forced to choose a sub-optimal plan. • The problem: Query generators can produce very complex queries and databases are getting bigger. • The penalty for poor planning is getting larger Daniel Ballinger

  3. QUERY QUANTIFER partno descr suppno distinct=true HEAD q1.descr =q1.partno q2.suppno q1(F) q2(F) q4(A) q1.partno = q2.partno q2.priceq4.price BODY distinct=ENFORCE q1.descr = ‘engine’ SELECT DISTINCT q1.partno, q1.descr, q2.suppno FROM inventory q1, quotations q2WHERE q1.partno = q2.partno AND q1.descr=‘engine’AND q2.price  ALL ( SELECT q3.price FROM quotations q3 WHERE q2.partno=q3.partno); LOCAL PREDICATE SUBQUERY distinct=false price =q3.price Tuple Flow q2.partno = q3.partno distinct=PERMIT JOIN PREDICATE q3(F) inventory quotations BASE TABLES Diversion: The Query Graph Model (QGM) SELECT SELECT partno,descr QUANTIFIER COLUMNS partno,price Daniel Ballinger

  4. The Proposed Solution - Rule Based Query Rewrite (QRW) • The goals of query rewrite • Make queries as declarative as possible • Transform “procedural” queries • Perform unnesting/flattening on nested blocks • Retain the semantics of the query (same answer) • How? • Perform natural heuristics E.g. “predicate pushdown” • Production rules encapsulate a set of Query Rewrite heuristics. • A Single Rewrite Philosophy • “Whenever possible, a query should be converted to a single select operator” • The Result • The Standard optimiser is given the maximum latitude possible Daniel Ballinger

  5. head.distinct = true SELECT DISTINCT d.deptno, v.lastname FROM View v, Dept d WHERE v.empno=d.mgmo v(F) View SELECT empno, lastname FROM Emp, Project WHERE salary<20000 AND workno=projno AND pbudget>500000 Dept Emp Project SELECT DISTINCT d.deptno,e.lastname FROM Emp e, Dept d, Project p WHERE e.empno=d.mgmo ANDe.salary<20000 AND e.workno=p.projno AND p.pbudget>500000 Dept Emp Project An Example of Rule 1 - SELECT Merge Daniel Ballinger

  6. Conclusions and Comments • The problem: Complex SQL queries can contain nested blocks that can’t be optimised using the standard plan optimiser. • The solution: By rewriting the query to a semantically equivalent query with fewer boxes the (near) optimal plan can be found. • The QGM provides an abstract view of queries that is suitable for most rule transformations. • Mechanisms are provided for dealing with duplicates. • Examples given in the paper show improvements by orders of magnitude • Query Rewrite has become part of DB2 and Oracle Daniel Ballinger

More Related