Examples of Index Nested Loops
Hash-index (Alt. 2) on sid of Sailors (as inner):
- Scan Reserves: 1000 page I/Os, 100*1000 tuples.
- For each Reserves tuple: 1.2 I/Os to get data entry in index, plus 1 I/O to get (the exactly one) matching Sailors tuple. Total: 220,000 I/Os.
Hash-index (Alt. 2) on sid of Reserves (as inner):
- Scan Sailors: 500 page I/Os, 80*500 tuples.
- For each Sailors tuple: 1.2 I/Os to find index page with data entries, plus cost of retrieving matching Reserves tuples. Assuming uniform distribution, 2.5 reservations per sailor (100,000 / 40,000). Cost of retrieving them is 1 or 2.5 I/Os depending on whether the index is clustered.