Alternative Plan 2(With Indexes)

• With clustered index on bid of Reserves, we get 100,000/100 = 1000 tuples on 1000/100 = 10 pages.

• INL with pipelining (outer is not materialized).

• Decision not to push ratingɱ before the join is based on

availability of sid index on Sailors.

• Cost: Selection of Reserves tuples (10 I/Os); for each,

must get matching Sailors tuple (1000*1.2); total 1210 I/Os.

• Join column sid is a key for Sailors.
  • At most one matching tuple, unclustered index on sid OK.

• Projecting out unnecessary fields from outer doesn’t help.

Reserves

Sailors

sid=sid

bid=100

sname

(On-the-fly)

rating > 5

(Use hash

index; do

not write

result to

temp)

(Index Nested Loops,

with pipelining )

(On-the-fly)

Previous slide

Next slide

Back to first slide

View graphic version