- Can enhance the speed of data retrieval.
Because of this, secondary indexes are most useful in decision support applications.
- Do not affect data distribution.
- Can be a maximum of 32 defined per table.
- Can be composed of as many as 64 columns.
- For a value-ordered NUSI, only a single numeric or DATE column of four or fewer bytes may be specified for the sort key.
- For a hash-ordered covering index, only a single column may be specified for the hash ordering.
- Can be created or dropped dynamically as data usage changes or if they are found not to be useful for optimizing data retrieval performance.
- Require additional disk space to store subtables.
- Require additional I/Os on inserts and deletes.
Because of this, secondary indexes might not be as useful in OLTP applications.
- Should not be defined on columns whose values change frequently.
- Should not include columns that do not enhance selectivity.
- Should not use composite secondary indexes when multiple single column indexes and bit mapping might be used instead.
- Composite secondary index is useful if it reduces the number of rows that must be accessed.
- The Optimizer does not use composite secondary indexes unless there are explicit values for each column in the index.
- Most efficient for selecting a small number of rows.
- Can be unique or nonunique.
- NUSIs can be hash-ordered or value-ordered, and can optionally include covering columns.
- Cannot be partitioned, but can be defined on a table with a partitioned primary index.