Below lists three queries (A)-(C), two tables and schema, and indices I1- I6.
For each of three queries (A)-(C) below, show two different query plans P1 and P2 such that (i) both use one
or more indices I1 to I6 shown below, (ii) P1 is most efficient and (iii) P2 is less efficient than P1. Here, use
show algebra trees if possible. Explain your plans.
From Course C
WHERE grade > 2015 AND
credit = 2
A: I5, B: I2, C: i4
FROM Student S
WHERE S.age > 20 AND S.sid
IN (SELECT C.sid
FROM Course C
WHERE C.credit = 4
AND C.grade >= 80)
SELECT S.enroll, COUNT(S.enroll)
FROM Student S
WHERE EXISTS (SELECT C.cid
FROM Course C WHERE C.sid =
S.sid AND C.grade < 70)
Student(sid: integer, sname: string, age: integer, enroll: integer)
Course(cname: string, sid: integer, credit: integer, grade: integer)
I1: clustered, hash index on <sid> of Student
I2: clustered, B+ index on <grade> of Course
I3: unclustered, B+ index on <grade, credit> of Course
I4: unclustered, B+ index on <grade, sid> of Course
I5: unclustered, B+ index on <enroll> of Student
I6: unclustered, hash index on < age, enroll> of Student
1. Answer the following questions on Query (A).
(a) Regarding on the selection conditions on “grade” and “credit”, which one will have higher reduction
factor? Also, explain assumptions you used to derive your answer.
(b) Show the most efficient query plan, using one of the indices from I1-16.
(c) Show a new index that can be used for an index-only query plan for Query (A).
2. Answer the following questions on Query (B).
(a) Transform Query (B) into a non-nested SQL query.
(b) Show the relational algebra tree for the answer of (a), where projections and selections need to be
(c) Show an efficient query plan, by choosing indices. Discuss which index in I2, I3 and I4 should be
3. Answer the following questions on Query (C).
(a) Show an efficient query plan, which uses indices from I1-16.
(b) Design a more efficient query plan for Query (C), by adding a new index to I1-16.
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: firstname.lastname@example.org 微信:itcsdx