Secondary Index

• Secondary Indexes provide an alternate path to the data.
• So far we have learned that every table has one and only one Primary Index and we have learned that the Primary Index is much faster than the Full Table Scan.
• Secondary Indexes are not as fast as the Primary Index, but they can be pretty fast, and they can be much faster than a Full Table Scan.
• There can be up to 32 Secondary Indexes on a table.
• Every Secondary Index creates a Subtable on every AMP designed to point to the real Primary Index Row-ID.
• There are two types of Secondary Index and they are Unique Secondary Indexes, which are called USIs and Non-Unique Secondary Indexes called NUSIs.
• An USI is always a Two-AMP operation so it is almost as fast as a Primary Index, but a NUSI is an All-AMP operation, but not a Full Table Scan.

The Secondary Index Subtable

• If users want another access path to the data the secondary index is the option.
• As soon as the USI is created with the SQL syntax the next move comes from Teradata creating a Subtable on every AMP. This is true for both the USI and the NUSI.
• Let’s say for example the DBA created the maximum of 32 secondary indexes on a table. Then there would be 32 Subtables created, each taking up PERM Space.
• The entire purpose for the Secondary Index Subtable will be to point back to the real row in the base table via the Row-ID.

USI – Always a Two-AMP Operation

• Secondary Indexes provide an alternate path to the data.
• Teradata runs extremely well without Secondary Indexes, but since secondary indexes use up space and overhead, they should only be used on “KNOWN QUERIES” or queries that are run over and over again.
• Every time the Parsing Engine sees the USI column in the WHERE clause it comes up with a plan that involves only two AMPs. USI query is a two-AMP operation.

NUSI Subtable is AMP-Local

• The NUSI Subtable is always AMP-Local.
• A NUSI is a Non-Unique Secondary Index, obvious again meaning that the value is Non-Unique and there could be thousands, millions or even billions of duplicates.
• So the Parsing Engine takes on a different strategy when building the NUSI Subtable. Each row in the Subtable only tracks the Base rows on the same AMP. This is what is meant by AMP-Local.
• On the above fig you can see that the AMP labeled “A Typical AMP” holds two base rows of the Employee_Table. The First_Name values, which was the column we created the NUSI Index on holds two values on this AMP, which are “Rakish” and “Vu‟. So in this typical AMPs Subtable there will be two rows tracking “Rakish” and “Vu‟. A NUSI Subtable is always created on each AMP, but in each AMPs Subtable are only values local to the base rows for that AMP.
• USI rows are hashed and NUSI rows are AMP-Local.
• USI query is always a two-AMP operation.
• A NUSI query is an All-AMP operation, but not a full table scan.
• An USI query is much faster than a NUSI! The Parsing Engine will use an USI at a moment’s notice, but it will not always choose to use a NUSI. Sometimes it will choose a Full Table Scan over a NUSI.
• The Parsing Engine will never choose a Full Table Scan over an USI.