Secondary Indexes (SIs) enable access to information in a table by alternate paths and can improve performance by avoiding full table scans.
SIs add to table overhead in terms of disk space and maintenance, but you can drop and recreate them as needed.
SIs have these characteristics:
- Do not affect the distribution of rows across AMPs.
- Can be unique or nonunique.
- Can be created for complex data types, such as geospatial data.
- Are used by the Optimizer when they can improve query performance.
- Can be useful for NoPI tables.