From SQL parse trees to RA expression trees

From SQL parse trees to RA expression trees

Query StarsIn(title,year,starName) MovieStar(name,address,gender,birthdate) Query: Give titles of movies that have at least one star born in 1960 SELECT title FROM StarsIn, MovieStar WHERE starName = name AND birthdate LIKE '%1960%' ;

<SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> , <FromList> AND titleStarsIn <RelName> MovieStar <Condition><Condition> <Attribute> = <Attribute> <Attribute> LIKE <Pattern> starNamenamebirthdate ‘%1960’ Parse Tree <Query>

Rules (1) • If we have a <Query> with a <Condition> that has no subqueries, then replace the entire construct – the select-list, from-list, and condition – by a relational-algebra expression (tree) consisting, from bottom to top, of: • The product of all the relations mentioned in the <FromList>, which is the argument of: • A selection C, where C is the <Condition> expression in the construct being replaced, which in turn is the argument of: • A projectionL,where L is the list of attributes in the <SeIList>.

RA tree

Queries Involving Views (1) Operands in a query might be virtual views. View: CREATE VIEW ParamountMovies AS SELECT title, year FROM Movies WHERE studioName = 'Paramount'; Views are represented by RA trees as well. Query: SELECT title FROM ParamountMovies WHEREyear = 1979; Leaf will be the view.

Queries Involving Views (2) • To get query over base tables, we substitute, for each view-leaf, the root of the tree that defines that view. Simplifying the query over base tables

Queries Involving Views (3) • Of course there might be more than one view used in a query.

Subqueries in Conditions Example SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE '%1960' ); intermediate form of operator, called two-argument selection.

Removing Subqueries From Conditions Case 1: Uncorrelated subqueries Condition that equates each component of a tuple t to the corresponding attribute of relation S. Don’t produce more copies of tuples than the original query does…

Removing Subqueries From Conditions Case 1: Example No  here because name is a key for movie stars…

Removing Subqueries From Condition Case 2: Correlated subqueries Example Find the movies where the average age of the stars was at most 40 when the movie was made. SELECT DISTINCT mi.movieTitle, mi. movie Year FROM StarsIn m1 WHERE m1. movie Year - 40 <= ( SELECT AVG(birthdate) FROM StarsIn m2, MovieStar s WHERE m2. starName = s. name AND m1.movieTitle = m2.movieTitle AND m1. movieYear = m2.movieYear );

Removing Subqueries From Condition Case 2: Correlated subqueries Intermediate form Solution: Defer this selection…i.e. pull up the selection. How?

Removing Subqueries From Condition Case 2: Correlated subqueries Solution

Removing Subqueries From Condition Case 2: Correlated subqueries Improving solution: The join between StarsIn m1 and the rest of the expression equates the title and year attributes from StarsIn m1 and StarsIn m2.

Oracle Plan

PLAN_TABLE • PLAN_TABLE holds execution plans generated by the EXPLAIN PLAN statement. • Important columns in PLAN_TABLE: statement_id Unique identifier for each execution plan operation The operation performed in one step of the execution plan, such as “table access” options Additional information about the operation, such as “by index ROWID” object_name Name of table, index, view, etc. accessed id Step number in execution plan parent_id Step number of parent step

EXPLAIN PLAN statement EXPLAIN PLAN FOR SELECT /*+ RULE */ m1.movieTitle, m1.movieYear FROM StarsIn m1 WHERE m1.movieYear-40 <=( SELECT AVG(birthdate) FROM StarsIn m2, MovieStar s WHERE m2.starName=s.name AND m1.movieTitle=m2.movieTitle AND m1.movieYear = m2.movieYear ); This inserts the query plan into the PLAN_TABLE. We better delete any previous content of the table.

Extracting the plan tree COL operation FORMAT a35 COL object_name FORMAT a30 SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' || options AS operation, object_name FROM PLAN_TABLE START WITH id = 0 CONNECT BY PRIOR id = parent_id;

The plan ID PARENT OPERATION OBJECT_NAME ---- ------ ----------------------------------- ------------------------------ 0 SELECT STATEMENT 1 0 FILTER 2 1 TABLE ACCESS FULL STARSIN 3 1 SORT AGGREGATE 4 3 MERGE JOIN 5 4 SORT JOIN 6 5 TABLE ACCESS FULL MOVIESTAR 7 4 SORT JOIN 8 7 TABLE ACCESS FULL STARSIN

Execution Plan Operations FILTER Read a set of row data and discard some rows based on various criteria. To determine the criteria, operations from a second input may need to be performed.Input:rows to be examined and, sometimes, an additional subordinate operation that must be performed for each row from the first input in order to evaluate criteria.Output:the rows from the first input that met the criteria.

Execution Plan Operations TABLE ACCESS FULL Perform a full table scan of the indicated table and retrieve all rows that meet criteria from the WHERE clause. INDEX UNIQUE SCAN Look up a complete key in a unique index. INDEX RANGE SCAN Look up a key in a non-unique index, or an incomplete key in a unique index. TABLE ACCESS BY INDEX ROWID Look up rows in a table by their ROWIDs.

Execution Plan Operations NESTED LOOPS Perform a join between two sets of row data using the nested loops algorithm. HASH JOIN Perform a join between two sets of row data using the hash join algorithm.

Execution Plan Operations SORT JOIN The input is sorted by the join column or columns in preparation for a join using the merge join algorithm. MERGE JOIN Perform a join between two sets of row data using the merge join algorithm. SORT ORDER BY Sort the input rows for the purpose of satisfying an ORDER BY clause. SORT GROUP BY The rows are sorted and grouped to satisfy a GROUP BY clause.

From SQL parse trees to RA expression trees