This section describes Unique Secondary Indexes (USIs) and Nonunique Secondary Indexes (NUSIs).
When column demographics suggest their usefulness, the Optimizer selects secondary indexes to provide faster single row or set selection, depending on whether the index is a USI or a NUSI, respectively.
A secondary index is never required for database tables, but can often improve system performance, especially in decision support environments.
While secondary indexes are useful for optimizing repetitive and standardized queries, Vantage is also highly optimized to perform full-table scans in parallel. Because of the strength of full-table scan optimization, there is little reason to be heavy-handed about assigning multiple secondary indexes to a table.
You can create secondary indexes when you create the table or add them later using the CREATE INDEX . Unlike primary indexes, secondary indexes can be dropped and created.
For more information about how the database accesses data using a unique secondary index, see Unique Secondary Indexes.
For more information about how the database accesses data using a nonunique secondary index, see Nonunique Secondary Indexes.
For design issues related to secondary index support for tactical queries, see Localizing the Work.
Keep the statistics on your secondary indexes as current as possible.
The recommended practice for recollecting statistics is to set appropriate thresholds for recollection using the THRESHOLD options of the COLLECT STATISTICS statement. See COLLECT STATISTICS (Optimizer Form) for details on how to do this.