Nested Queries

• Nested block is optimized independently, with the outer tuple considered as providing a selection condition.

• Outer block is optimized with the cost of `calling’ nested block computation taken into account.

• Implicit ordering of these blocks means that some good strategies are not considered. The non-nested version of the query is typically optimized better.

SELECT S.sname

FROM Sailors S

WHERE EXISTS

(SELECT *

FROM Reserves R

WHERE R.bid=103

AND R.sid=S.sid)

Nested block to optimize:

SELECT *

FROM Reserves R

WHERE R.bid=103

AND S.sid= outer value

Equivalent non-nested query:

SELECT S.sname

FROM Sailors S, Reserves R

WHERE S.sid=R.sid

AND R.bid=103

Previous slide

Next slide

Back to first slide

View graphic version