SECONDARY INDEX

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