- Consider naming secondary indexes whenever possible using a standard naming convention.
- Avoid assigning secondary indexes to frequently updated column sets.
- Avoid assigning secondary indexes to columns with lumpy distributions because there is a slight chance the Optimizer may mistake their usefulness.
- Avoid creating excessive secondary indexes on a table, particularly for a table used heavily, or even moderately, for OLTP processing. The less frequently the table is updated, the more desirable a multiple index solution.
- Consider building secondary indexes on column sets frequently involved in the following clauses, predicates, and other logical operations:
- Selection criteria
- Join criteria
- ORDER BY clauses
- GROUP BY clauses
- Foreign keys (because of join and subquery processing)
- UNION, DISTINCT, and other sort operations
When these operations act on well-indexed column sets, the number of scans and sorts that must be performed on the data by the database manager can be greatly reduced.
- Consider creating USIs for tables without a UPI that require frequent single-row access.
- Consider creating NUSIs for tables that require frequent set selection access.
- Consider creating covering indexes when possible and cost effective (including considering the cost of maintaining the index). The Optimizer frequently selects covering indexes to substitute for a base table access whenever the overall cost of the query plan is reduced. Such index-only access promotes faster retrievals.
Alternatively, applications may be well served by join indexes, which may be used profitably in situations where multiple columns are frequently joined. See Join Indexes.
- Consider creating secondary indexes on columns frequently operated on by built-in functions such as aggregates.
- Consider assigning a uniqueness constraint such as PRIMARY KEY, UNIQUE, or USI, as appropriate, to the primary or other alternate key of any table built with a NUPI. This both enforces uniqueness, eliminating the burden of making row uniqueness checks, and enhances retrieval for applications where the primary or other alternate key is frequently used as a selection or join criterion.
This guideline is situational and contingent on multiple factors. The factors involved in the recommendation are described in Using Unique Secondary Indexes to Enforce Row Uniqueness.
A primary or alternate key USI may not be a good decision for a table that is frequently updated by OLTP applications.
- Plan to dynamically drop and recreate secondary indexes to accommodate specific processing and performance requirements such as bulk data loading utilities, database archives, and so on.
Create appropriate macros to perform these drop and create index operations if you need to undertake such specific processing tasks regularly.
- Make sure your indexes are being used as planned by submitting EXPLAIN request modifiers to audit index selection for queries the indexes are designed to facilitate.Secondary indexes that are never selected by the Optimizer are a burden to the system for the following reasons:
- Secondary indexes consume disk resources otherwise used to store data or indexes that are used.
- Secondary indexes degrade update processing performance unnecessarily.
The following rules of thumb and performance considerations apply to selecting a unique or nonunique column set as a secondary index for a table.