Secondary Indexes

Secondary Indexes

1. Secondary Indexes provide another path to access data.
2. Teradata allows up to 32 secondary indexes per table.
3. Keep in mind; row distribution of records does not occur when secondary indexes are defined. 
4. The value of secondary indexes is that they reside in a subtable and are stored on all AMPs, which is very different from how the primary indexes (part of base table) are stored.
5. Keep in mind that Secondary Indexes (when defined) do take up additional space. 
6. Secondary Indexes are frequently used in a WHERE clause.
7.  The Secondary Index can be changed or dropped at any time.
8.  However, because of the overhead for index maintenance, it is recommended that index values should not be frequently changed.

There are two different types of Secondary Indexes

1.Unique Secondary Index (USI)

2.Non-Unique Secondary Index (NUSI).

Unique Secondary Index:

1. Unique Secondary Indexes are extremely efficient. 
2. A USI is considered a two-AMP operation. 
3. One AMP is utilized to access the USI subtable row (in the Secondary Index subtable) that references the actual data row, which resides on the second AMP.

 Non Unique Secondary Index:

1. A Non-Unique Secondary Index is an All-AMP operation and will usually require a spool file.
2.  Although a NUSI is an All-AMP operation, it is faster than a full table scan.

 Secondary indexes can be useful for:

•       Satisfying complex conditions

•       Processing aggregates

•       Value comparisons

•       Matching character combinations

•       Joining tables

A secondary index can be defined as a Unique Secondary Index (USI) or Non-Unique Secondary Index (NUSI). 

Syntax: 

CREATE INDEX <index name> (<column list>) ON table name;