Secondary Indexes (SIs) allow access to information in a table by alternate paths, and can improve performance by avoiding full table scans.
Although SIs add to table overhead, in terms of disk space and maintenance, you can drop and recreate SIs as needed.
SIs:
- 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 the indexes can improve query performance.
- Can be useful for NoPI tables.