The SELECT statement
SELECT A
FROM T
WHERE P
accesses a table, T, with attributes A, B, C and K, where K is the primary key, and P is a predicate.
The values of all attributes are randomly chosen over the integer domain. Since the table
is updated frequently, there is a limitation of at most one clustered and one unclustered index.
In each of the following parts choose the indexes (type, search key, clustered or unclustered)
to optimize the SELECT statement and maintain the primary key constraint when the value
of P is as specied. In each case give the query plan you expect the DBMS to choose for the
SELECT and the reason you think that plan is best. Do not use index covering.
(a) P is (B = 10)
(b) P is (B > 10)
(c) P is (B > 10 AND C = 5)
(d) P is (B > 10) and the WHERE clause is followed by the clause ORDER BY A
(a) P is (B = 10)
Solution:
clustered index on B; unclustered on K
search on B=10, scan (B+ tree) or search bucket (hash) for all satisfying rows
(b) P is (B > 10)
Solution:
clustered B+ tree on B, unclustered on K
rows are ordered on B, search for B=10 and scan from that point
(c) P is (B > 10 AND C = 5)
Solution:
Fewer rows satisfy C=5 than B>10 (values are randomly chosen). Hence use clustered
index on C, clustered on K; hash or B+ tree OK for both.
Fetch rows satisfying C=5 and return those having B>10
d) P is (B > 10) and the WHERE clause is followed by the clause ORDER BY A
Solution:
Clustered B+ tree on A, unclustered hash or B+ on K. Scan entire le, discard rows return
rows with B>10. This is better than clustering on B since roughly half of the rows satisfy
B>10 and they would require sorting.
You might also like to view...
The ________ action provides a useful tool in a database as it can be utilized to provide feedback to the user that the macro did indeed run
A) MsgBox B) Message C) # MsgBox D) Message Box
To increase the width of a field in a Base table, the user can either drag or ________ the column border
Fill in the blank(s) with correct word
Which stakeholder is the officer that leads any security effort and may report directly to the CEO?
A. CSO B. executive-level management C. CFO D. business unit management
A subclass is any class that inherits attributes from a superclass.
Answer the following statement true (T) or false (F)