Selecting Indexes - Physical Database Design - page 57

so an application developer should be careful to inspect a tool's output to see if any
performance problems exist. Caution should be exercised when removing indexes;
make sure that there is no performance degradation due to these removals.
Rule 6. Add indexes only when absolutely necessary . When redesigning indexes,
either you have too many or too few indexes already. You have too few indexes and
need more when you are doing unnecessary table scans for certain queries that
return only a fraction of the rows in the table [Hubel 2001].
Rule 7. Add or delete index columns for composite indexes to improve perfor-
mance. Do not alter primary key columns. Multiple-column composite indexes
can be very efficient for certain complex queries. For one thing, they reduce the
number of sorts and merges required for multiple single-index searches. Also, addi-
tional columns can help with the execution of joins across tables. On the other
hand, the more columns used in the index, the more restrictive the applicability
and usefulness of the index for queries that don't specify exactly the attributes
defined in the index. Performance tradeoffs must be carefully considered when add-
ing or deleting columns.
For primary keys, adding or deleting columns will tend to change the uniqueness of
the key and affect the integrity of any applications that use it. Therefore, in general,
avoid altering primary key columns.
Rule 8. Use attributes for indexes with caution when they are frequently updated.
Some expert developers simply index all FKs regardless of how often they are
updated and only make adjustments if serious bottlenecks appear. In general, when
attributes (columns) are frequently updated, both the indexes and stored rows con-
taining those attribute values must be updated. If the cost (I/O time) of those
updates, when using an index, is significantly larger than the benefit (I/O time) of
faster queries using that index, then the index should not be created.
Rule 9. Keep up index maintenance on a regular basis; drop indexes only when
they are clearly hurting performance. Index maintenance is needed constantly
when table rows are constantly updated with insertions, deletions, and changes.
However, as we mentioned in Chapter 2, index maintenance can significantly hurt
performance of many writing commands such as INSERT, UPDATE, DELETE,
IMPORT, and LOAD. When performance data collection indicates that an index
has become the bottleneck, either reorganize the index or delete it. If updates are
done during the day and queries are done at night, it may be useful to delete the
indexes at the beginning of the day and rebuild them at the end of the day when
queries are done. Again, caution should be observed when deleting indexes—seri-
ous problems could arise when foreign key constraints are involved.
DBAs are known to sometimes drop indexes before a large data import, and then
rebuild the indexes afterward. The incremental cost of adding an index can actually
be greater than the cost of mass building the index. For mass building, we assume