-
Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amount 6000, 7000, 8000, 9000 and 10000 were each paid by 20% of the students. Consider these query plans (Plan1 on left, Plan2 on right) to “list all courses taken by students who have paid more than x”:
A disk seek takes 4 ms, disk data transfer bandwidth is 300 MB/s and checking a tuple to see if amount is greater than x takes 10 µs. Which of the following statements is correct?
-
- Plan1 and Plan2 will not output identical row sets for all databases
- A course may be listed more than once in the output of Plan1 for some databases
- For x = 5000, Plan1 executes faster than Plan2 for all databases
- For x = 9000, Plan1 executes slower than Plan2 for all databases
- Plan1 and Plan2 will not output identical row sets for all databases
Correct Option: C
Given that left hand side is plan1 and right hand side is plan2 .
Plan1
1. select records form paid
2. joins them
Plan2
1. joins records from paid
2. records are checked
The seek time of disk is 4 ms and data transfer rate is 300 MB/s.
So, if x = 5000. Although the output remains the same but the plan1 executes faster than plan2 for all databases.
