A secondary index is an alternate path to the data. Secondary Indexes are used to improve performance by allowing the user to avoid scanning the entire table. A database designer typically chooses a secondary index because it provides faster set selection. secondary indexes require at least two and possibly all AMPs, depending on the index and the type of operation. A secondary index search will typically be less expensive than a full table scan.
There are two types of secondary indexes.
- Unique Secondary Index (USI)
- Non-Unique Index (NUSI)
Secondary indexes are typically defined to improve performance with regard to residual conditions in the where clause. One of the more common uses for a Unique Secondary Index arises with the need to maintain uniqueness on a column or columns that are NOT the primary index.
Then SI comes into the picture to enhance performance and chance of avoiding FTS. They can be created and dropped anytime. If a non PI column is being used in where clause, define SI on it you can use USI to enforce uniqueness in a PPI table where partition columns are not part of PI.Understanding the business requirement and design, drives us to create Secondary Index.
No comments:
Post a Comment