Consider the following relations: Salaries range from $10,000 to $100,000, ages

Question

Consider the following relations:

Salaries range from $10,000 to $100,000, ages vary from 20 to 80, each department has about five employees on average, there are 10 floors, and budgets vary from $10,000 to $1 million. You may assume the following:

Nested loop joins (NLJ) and index nested loop joins (INLJ) are supported.

a hash index on Dept.floor has been created

a B+ tree index on (Emp.age,Emp.salary) has been created

a B+ tree index on Dept.did has been created

Draw at least 4 different query execution plans for the following query.

Explanation / Answer

Query Execution Plan :

Scan Table Emp

Using B+ tree on Emp.age where emp.age<30 and emp.salary>70000

scan table Dept

using B+ tree Dept.did=Emp.did and D.floor = 10

Scan Table Dept

using B+ tree Dept.did=Emp.did and D.floor = 10

scan table Emp

Using B+ tree on Emp.age where emp.age<30 and emp.salary>70000

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects