Extensible/Rule Based Query Rewrite Optimization in Starburst

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