1 / 9

CPS216: Advanced Database Systems Query Rewrite Rules for Subqueries

CPS216: Advanced Database Systems Query Rewrite Rules for Subqueries. Shivnath Babu. More Query Rewrite Rules. Transform one logical plan into another Do not use statistics Equivalences in relational algebra Push-down predicates Do projects early Avoid cross-products if possible

kennethcash
Download Presentation

CPS216: Advanced Database Systems Query Rewrite Rules for Subqueries

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. CPS216: Advanced Database SystemsQuery Rewrite Rules for Subqueries Shivnath Babu

  2. More Query Rewrite Rules • Transform one logical plan into another • Do not use statistics • Equivalences in relational algebra • Push-down predicates • Do projects early • Avoid cross-products if possible • Use left-deep trees • Use of constraints, e.g., uniqueness • Subqueries  Joins (we will study this rewrite rule after we do physical plan selection)

  3. SQL Query with an Uncorrelated Subquery Find the movies with stars born in 1960 MovieStar(name, address, gender, birthdate) StarsIn(title, year, starName) SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE ‘%1960’ );

  4. Parse Tree <Query> <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> <Tuple> IN <Query> title StarsIn <Attribute> ( <Query> ) starName <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> <Attribute> LIKE <Pattern> name MovieStar birthDate ‘%1960’

  5. title Two-argument selection  StarsIn <condition> <tuple> IN name <attribute> birthdate LIKE ‘%1960’ starName MovieStar Generating Relational Algebra

  6. Rewrite Rule for Two-argument Selection with Conditions Involving IN Two-argument selection  <condition>  X Lexp <condition> Lexp δ <tuple> IN Rexp Rexp

  7. title starName=name  StarsInδ name birthdate LIKE ‘%1960’ MovieStar Applying the Rewrite Rule title  StarsIn <condition> <tuple> IN name <attribute> birthdate LIKE ‘%1960’ starName MovieStar

  8. title title starName=name starName=name  StarsIn name StarsInδ birthdate LIKE ‘%1960’ name birthdate LIKE ‘%1960’ MovieStar MovieStar Improving the Logical Query Plan

  9. SQL Query with an Correlated Subquery MovieStar(name, address, gender, birthdate) StarsIn(title, year, starName) SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE name LIKE ‘Tom%’ and year = birthdate + 30 ); Can we rewrite this query as a single-block join query?

More Related