6. Consider the following query Q over R(A,B,C) and S(D,E,F): SELECT A,F FROM R, S WHERE C=D AND A < 1000 AND E='e' Assume there is a clustering index on E of S, an index on D of S, an index on C of R, and a clustering index on A of R. Assume the indices have equal height. Assume R and S have roughly equal sizes (in both number of blocks and number of records), and assume that σE='e'(S) is twice the size of σA<1000(R). 6.a Draw the canonical query tree for the query Q. 6.b Transform the canonical query tree for Q into a final query tree that is efficient to execute. 6.c Describe the best query evaluation plan (with minimal cost) for Q for the information given. 6.d Draw another left-deep query tree whose cost is higher than the one given in 6.c. 6.e Give one example instance of relational algebra rewriting, for each rewriting rule that was used in the trees of 6.a and 6.b.
Q6. Consider the following query Q over R(A,B,C) and S(D,E,F):
SELECT A,F
FROM R, S
WHERE C=D AND A < 1000 AND E='e'
Assume there is a clustering index on E of S, an index on D of S, an index on C of R, and a clustering index on A of
R. Assume the indices have equal height. Assume R and S have roughly equal sizes (in both number of blocks and
number of records), and assume that σE='e'(S) is twice the size of σA<1000(R).
6.a Draw the canonical query tree for the query Q.
6.b Transform the canonical query tree for Q into a final query tree that is efficient to execute.
6.c Describe the best query evaluation plan (with minimal cost) for Q for the information given.
6.d Draw another left-deep query tree whose cost is higher than the one given in 6.c.
6.e Give one example instance of relational algebra rewriting, for each rewriting rule that was used in the trees of 6.a
and 6.b.
Answer:
Step by step
Solved in 2 steps
