Consider the schema
Employee(Id: INTEGER, Name: STRING, Deptid: STRING, Salary: INTEGER)
Department(DeptId: INTEGER, Name: STRING)
Propose indexes appropriate for evaluating the query
SELECT D.Name, E.Name
FROM Employee E, Department D
WHERE E.DeptId = D.DeptId AND E.Salary > :sal
(a) when it is expected that sal will be a large number.
(b) when it is expected that sal will be a small number.
(a) when it is expected that sal will be a large number.
Solution:
With sal a large number, only a few rows of E will qualify. Hence an ecient query plan
would be one that uses a B+ tree index on Employee with search key Salary to fetch those
rows in the outer loop of an index-nested loop join. For each row, the corresponding row
of Department can be fetched in the inner loop using an index with search key DeptId.
(b) when it is expected that sal will be a small number.
Solution:
Since a large result set is expected an index-nested loop join will generally be inecient.
A sort-merge join will be better, especially if the tables are pre-sorted by the presence of
clustered B+ tree indexes on the join attributes: D.DeptId and E.DeptId.
You might also like to view...
A database operation that can be used to merge and retrieve data stored in more than one table view is called a SELECT.
Answer the following statement true (T) or false (F)
A(n) ____ provides controls for a game so that the user can play the game.
A. game engine B. user interface C. math engine D. physics engine
On a network, _______ is the elapsed time for data to make a round-trip from point A to point B and back to point A.
Fill in the blank(s) with the appropriate word(s).
An administrator needs to back up changed files only and has time constraints on the back up window. Which of the following is the BEST choice for a backup strategy?
A. Differential B. Snapshot C. Full D. Incremental