Summary
Choosing what indexes to add to your tables remains one of those areas where creative tuning work can still trump mechanical attempts to measure and react. There are some index tuning wizards available for other databases, but even the best of them just provide suggestions instead of dependable advice. It's important to be systematic about your indexes though. Because adding an index increases overhead every time you add or change rows in a table, each index needs to satisfy enough queries to justify how much it costs to maintain. There is more information about determining if the indexes on your system are working effectively in Chapter 11, Database Activity and Statistics.
- Measure actual block reads to determine whether an index is truly effective.
- Queries cannot be answered using only the data in an index. The data blocks must be consulted for row visibility information in all cases.
- An index is only useful if it is selective; it can be used to only return a small portion...
