E xercise 16.7.1: Consider a relation R(a,b,c,d) that has a clustering index on
ID: 3708937 • Letter: E
Question
E xercise 16.7.1: Consider a relation R(a,b,c,d) that has a clustering index
on a and nonclustering indexes on each of the other attributes. The relevant
parameters are: B(R) = 1000, T(R) - 5000, V (R ,a) = 20, V(R,b) = 1000,
V(R,c) = 5000, and V(R,d) = 500. Give the best query plan (index-scan
or table-scan followed by a filter step) and the disk-I/O cost for each of the
following selections:
** Please read the Question Carefully, It asks for the Query Plan. Don;t Post already posted Solutions" on the website
a) Selection a= 1 AND b=2 AND d=3{R)-
b )selection a =1 AND b=2 AND c > 3 (R )-
c) selection a = l AND b<2 AND c>=3(R)
Explanation / Answer
Answer
a)
1.cost of checking all conditions through a table scan + filter step is
B(R) = 1000 block I/Os.
2. cost of an index scan for the given condition a=1 , followed by a filter step is
B(R)/V(R,a) = 1000/20 = 50 block I/Os.
3. cost of an index scan for the given condition b=2 , followed by a filter step is
T(R)/V(R,b) = 5000/1000 = 5 block I/Os.
4. cost of an index scan for the given condition d=3 , followed by a filter step is
T(R)/V(R,d) = 5000/500 = 10 block I/Os.
Among four the least one is 5 block I/Os , hence we select plan 3.
b)
1.cost of checking all conditions through a table scan + filter step is
B(R) = 1000 block I/Os.
2. cost of an index scan for the given condition a=1 , followed by a filter step is
B(R)/V(R,a) = 1000/20 = 50 block I/Os.
3. cost of an index scan for the given condition b=2 , followed by a filter step is
T(R)/V(R,b) = 5000/1000 = 5 block I/Os.
4. cost of an index scan for the given condition c>=3 , followed by a filter step is
T(R)/3 = 5000/3 = 1666.66 = 1667 block I/Os.
Among four the least one is 5 block I/Os , hence we select plan 3.
c)
1.cost of checking all conditions through a table scan + filter step is
B(R) = 1000 block I/Os.
2. cost of an index scan for the given condition a=1 , followed by a filter step is
B(R)/V(R,a) = 1000/20 = 50 block I/Os.
3. cost of an index scan for the given condition b<=2 , followed by a filter step is
T(R)/3 = 5000/3 = 1666.66 = 1667 block I/Os.
4. cost of an index scan for the given condition c>=3 , followed by a filter step is
T(R)/3 = 5000/3 = 1666.66 = 1667 block I/Os.
Among four the least one is 5 block I/Os , hence we select plan 2.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.