Relation R(a, b, c, d, e) has 5,000,000 records, with 10 records per block. • Fi
ID: 3696731 • Letter: R
Question
Relation R(a, b, c, d, e) has 5,000,000 records, with 10 records per block.
• Field a is an integer primary key with values between 0 and 5,000,000.
• Field b is real-valued with values between 20,000 and 200,000 and 100,000 unique values. • Field c is character data with 5,000,000 unique values.
There are three indexes, each of which can store up to 100 index records per block:
• a primary B+-tree index on a with height 4,
• a secondary B+-tree index on b with height 2,
• a hash index on c with 50 buckets.
Which indexes are likely to improve processing time for each of the queries below? (Your answer may be one index, more than one index, or “none”). Explain your answer using estimated numbers of block reads required to process the queries.
(a) SELECT * FROM R WHERE a=87555 AND b=60155.76 (b) SELECT * FROM R WHERE c=’abc’
(c) SELECT * FROM R WHERE a BETWEEN 100000 AND 110000 AND c=’abc’ (d) SELECT * FROM R WHERE a BETWEEN 100000 AND 110000 AND b=60155.76
(e) SELECT * FROM R WHERE a BETWEEN 100000 AND 110000 OR c=’abc’
Explanation / Answer
a)
In this case since the value of b is a exact real number a secondary B+-tree index on b with height 2 will be helpful and fastest.
b) In this case since query is directly linked with c a hash index on c with 50 buckets will be helpful.
c)
In this case both 1st and third option will be helpful.
d)
In this case option 1 and option 2 both will be helpful.
e)
In this case option 1 and option 3 will be helpful.