Criteria for Selecting a Secondary Index
The following rules of thumb and performance considerations apply to selecting a unique
or non-unique column set as a secondary index for a table.
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 might 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:
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 NoPI tables that require frequent single‑row access because
the only alternative is a full‑table scan.
Consider creating NUSIs for NoPI tables that require frequent set selection access
because the only alternative is a full‑table scan.
Consider creating a simple NUSI on geospatial columns that are frequently queried.
This is especially true for requests that contain geospatial predicate terms, geospatial
join terms, or both.
Note the following about geospatial secondary indexes:
You cannot create a USI on a geospatial column.
You cannot create a composite geospatial NUSI. Geospatial indexes can only be defined
on a single geospatial column.
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.
Many applications are well served by join indexes, which can be used profitably in
many covering situations where multiple columns are frequently joined. See Chapter 11: “Join and Hash Indexes” for further information about 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 is contingent on a number of factors. The various
factors involved in the recommendation are described in “Using Unique Secondary Indexes to Enforce Row Uniqueness” on page 457.
A primary or alternate key USI might 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.
Ensure that your indexes are being used as planned by submitting EXPLAIN request modifiers
to audit index selection for those queries they are designed to facilitate.
Indexes that are never selected by the Optimizer are a burden to the system for the
They consume disk resources that could profitably be used to store data or indexes
that are used.
They degrade update processing performance unnecessarily.
You can include UDT columns in a secondary index definition.
Never attempt to include columns defined with an XML, BLOB, CLOB, BLOB‑based UDT,
CLOB‑based UDT, XML‑based UDT, Period, JSON, ARRAY, or VARRAY data type in a secondary
You can define a simple NUSI, but not a composite NUSI, on a geospatial column.
You cannot include a column defined with a geospatial data type in a USI definition.
Never attempt to define a secondary index on a global temporary trace table. See SQL Data Definition Language.