Using an Index for Selections
Cost depends on #qualifying tuples and clustering.
- Cost of finding qualifying data entries (typically small) plus cost of retrieving records (could be large w/o clustering).
- In example, assuming uniform distribution of names, about 10% of tuples qualify (100 pages, 10000 tuples). With a clustered index, cost is little more than 100 I/Os; if unclustered, up to 10000 I/Os!
Important refinement for unclustered indexes:
1. Find qualifying data entries.
2. Sort the rids of the data records to be retrieved.
3. Fetch rids in order. This ensures that each data page is looked at just once (though # of such pages likely to be higher than with clustering).