From SQL parse trees to RA expression trees

1
From SQL parse trees to RA expression trees
2
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'
  

3
Parse Tree
ltQuerygt
4
Rules (1)

• If we have a ltQuerygt with a ltConditiongt 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 ltFromListgt, which is the argument of
• A selection ?C, where C is the ltConditiongt
  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 ltSeIListgt.

5
RA tree
6
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.

7
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
8
Queries Involving Views (3)

• Of course there might be more than one view used
  in a query.

9
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.
10
Removing Subqueries From Conditions

• Case 1 Uncorrelated subqueries

Condition that equates each component of a tuple
t to the corresponding attribute of relation S.
Dont produce more copies of tuples than the
original query does
11
Removing Subqueries From Conditions

• Case 1 Example

No ? here because name is a key for movie stars
12
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 lt (
• SELECT AVG(birthdate)
• FROM StarsIn m2, MovieStar s
• WHERE m2. starName s. name AND
• m1.movieTitle m2.movieTitle
  AND
• m1. movieYear m2.movieYear
• )

13
Removing Subqueries From Condition

• Case 2 Correlated subqueries
• Intermediate
• form

Solution Defer this selectioni.e. pull up the
selection. How?
14
Removing Subqueries From Condition

• Case 2 Correlated subqueries
• Solution

15
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.

16
Oracle Plan
17
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

18
EXPLAIN PLAN statement

• EXPLAIN PLAN FOR
• SELECT / RULE / m1.movieTitle, m1.movieYear
• FROM StarsIn m1
• WHERE m1.movieYear-40 lt(
• SELECT AVG(birthdate)
• FROM StarsIn m2, MovieStar s
• WHERE m2.starNames.name AND
• m1.movieTitlem2.movieTitle AND
• m1.movieYear m2.movieYear
• )
• This inserts the query plan into the PLAN_TABLE.
• We better delete any previous content of the
  table.

19
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

20
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

21
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.
22
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.
23
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.
24
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.