Find an execution plan for the following query tree:
Identify which of SL, SB, SH, SP, SC, and/or SS are applicable for the SELECT and which of JNL, JSL, and/or JSM are applicable for the JOIN in this query tree. Also provide a rationale for your answers.
For the SELECT: - SL is always applicable - you can always scan all the records - SB requires the records to be in order - since EMPLOYEE is not in order by Salary, there's no reason to expect that the result of the JOIN will be so ordered - SH requires things to be hashed - not applicable since the input to the SELECT comes from a JOIN - SP, SC, SS require indexes - not applicable since the input to the SELECT comes from a JOIN For the JOIN: - JNL is always applicable - JSL requires an appropriate index on the right-hand relation - it is not stated that there is an index on Dnumber in DEPARTMENT, but since Dnumber is the key, it is reasonable that there would be a primary index and so JSL would be applicable - JSM requires relations to be sorted - DEPARTMENT is ordered by Dnumber (its key) but EMPLOYEE is not ordered by Dno (not key), so EMPLOYEE would need to be sorted first in order for this to be applicable
Where there's a choice of algorithm, what choices do you expect to produce the best execution plan? Provide a rationale for your choices.
The only choice is for the JOIN. Cost for JNL: bR+(bRbS)/(n-2) = 2000+(2000*5)/(n-2) = 2000+10000/(n-2) Cost for JSL: bR+|R|(xB+1) = 2000+10000*(xB+1) Since xB ≥ 1, JNL actually turns out to be better in this case. (Why does that make sense?) JSM is also an option. Cost for JSM: bR+bS + the time to sort EMPLOYEE (DEPARTMENT is already sorted by Dnumber). Cost for sorting: 2b+2blogmin(n-1,b/n)(b/n)) = 2*2000+2*2000*logmin(n-1,2000/n)(2000/n)). This depends on n, but note that logmin(n-1,2000/n)(2000/n) will never be less than 1, so the cost for sorting EMPLOYEE will never be less than 8000 and the total cost for JSM will never be less than 10005. Since 2000+10000/(n-2) < 10005 for n ≥ 4, it is unlikely that JSM will be be better than JNL for this query tree.
Can anything be pipelined? Explain.
The SELECT can be pipelined - apply the condition to each row coming out of the JOIN. SL would need to be used to implement the SELECT. (It was the only choice anyway.)