12. Consider a relations with five fields: R(s,t,u,w). For each of the following
ID: 3904831 • Letter: 1
Question
12. Consider a relations with five fields: R(s,t,u,w). For each of the following query workloads (in- dependent of each other) add (at most) two indexes to Relation R to speed up the selection task. For each workload you need to define: (1) attribute(s) that index should cover, (2) if the index is clustered or unclustered, and (3) if you use a hash-based index or a B+ tree index. Provide your justification.
Workload I:
250,000 queries: select * from R where s < ? 20,000 queries: select * from R where t = ?
Workload II:
250,000 queries: select a, c from R where u < ? 25,000 queries: select * from R where s < ?
Workload II:
250,000 queries: select * from R where u <? and w = ? 25,000 queries: select * from R where s = ?
2,500 queries: select * from R where t = ?
Explanation / Answer
Workload 1:
a)Attributes used-s and t
b)The indexing is clustered as is more suitable for range queries. There can be more than one value of s which is greater than s.
c)B+ tree indexing is used as hash based index is not suitable for range queries. B+ tree indexing takes O(log n) while hash based index may result in full table scan O(n).
Workload 2
a) Attributes used-u and s
b) The indexing is clustered as clustering index is more suitable for range queries.
c)B+ tree indexing is used as hash based index is not suitable for range queries. B+ tree indexing takes O(log n) while hash based index may result in full table scan O(n).
Workload 3
a)Attributes used-u,w,s,t
b) The indexing is clustered as is more suitable for range queries. There can be more than one value of u which is greater than u.
c)B+ tree indexing is used as hash based index is not suitable for range queries. B+ tree indexing takes O(log n) while hash based index may result in full table scan O(n).
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.