Examples of Index Nested Loops
Hash-index on name of Person (as inner):
- Scan Purchase: 1000 page I/Os, 100*1000 tuples.
- For each Person tuple: 1.2 I/Os to get data entry in index, plus 1 I/O to get (the exactly one) matching Person tuple. Total: 220,000 I/Os. (36 minutes)
Hash-index on buyer of Purchase (as inner):
- Scan Person: 500 page I/Os, 80*500 tuples.
- For each Person tuple: 1.2 I/Os to find index page with data entries, plus cost of retrieving matching Purchase tuples. Assuming uniform distribution, 2.5 purchases per buyer (100,000 / 40,000). Cost of retrieving them is 1 or 2.5 I/Os depending on clustering.