All secondary indexes have the following properties:
- Can enhance the speed of data retrieval.
- Do not affect base table data distribution.
- Maximum of 32 secondary and join indexes defined per table. Each composite NUSI that specifies an ORDER BY clause counts as 2 consecutive indexes in this calculation (see Importance of Consecutive Indexes for Value-Ordered NUSIs).
The limit of 32 indexes applies to any combination of secondary and join indexes defined on a table, ranging from 0 secondary indexes and 32 join indexes, 11 join indexes, and 10 secondary indexes to 32 secondary indexes and 0 join indexes.
This includes the system-defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints.
- Can be composed of up to 64 columns.
- Cannot contain columns defined with XML, BLOB, CLOB, Period, or JSON data types.
- Cannot be defined on global temporary trace tables.
- Can be created or dropped dynamically as data usage changes or if found not to be useful for optimizing data retrieval performance.
- Require additional disk space to store subtables.
- Require additional I/Os on INSERTs, DELETEs, and possibly on UPDATEs and MERGEs.
- Must not be defined on columns whose values change frequently.
- Must not include columns that do not enhance selectivity.
- Must not use composite secondary indexes when multiple single-column indexes and bit mapping can be used.
- Most efficient for selecting a small number of rows.
- NUSIs can be hash-ordered or value-ordered.
- Ordering for NUSIs defined with an ORDER BY clause is restricted to a single numeric or DATE column of 4 of fewer bytes.
- If secondary indexes that fully or partially cover a query further improve their usefulness.
To be useful, a composite secondary index must reduce the number of rows that must be accessed.