Teradata Secondary Indexes

Secondary indexes are never required for Teradata Database tables, but they can often improve system performance.

A Secondary Index (SI) is an alternate data access path. It allows accessing the data without having to do a full-table scan.

The secondary index can be created explicitly using the CREATE TABLE and CREATE INDEX statements. Teradata Database can implicitly create unique secondary indexes.

For example, when we use a CREATE TABLE statement that specifies a primary index, Teradata Database implicitly creates unique secondary indexes on column sets that we specify using PRIMARY KEY or UNIQUE constraints.

Secondary Indexes are stored in separate subtables that require additional disk space and maintenance, which is handled automatically by the system. These tables are built-in all AMPs.

These subtables contain the index rows, so we need to add another set of rows that requires updating each time.

Secondary index subtables are also duplicated whenever a table is defined with FALLBACK. The Secondary index is not involved in the data distribution.

The purpose of the Secondary Index Subtable will be to return back to the real row in the base table via the Row-ID.

Secondary Index Rules

Here are some essential rules for the secondary index:

Rule 1: Secondary Indexes are optional.
Rule 2: Secondary Index values can be unique or non-unique.
Rule 3: Secondary Index values can be NULL.
Rule 4: Secondary Index values can be modified.
Rule 5: Secondary Indexes can be changed.
Rule 6: A Secondary Index has a limit of 64 columns.

Secondary Indexes Types

There are two types of secondary indexes in Teradata:

• Unique Secondary Index (USI)
• Non-Unique Secondary Index (NUSI)

Unique Secondary Index (USI)

A Unique Secondary Index allows unique values for the columns defined as USI.

A Unique Secondary Index (USI) serves two purposes.

• Enforces uniqueness on a column or group of columns.
• Speeds up access to a row (data retrieval speed).

When A USI is created, then the Teradata will immediately build a secondary index subtable on each AMP.

Each AMP will then hash the secondary index value for each of their rows in the base table.

Syntax

Below is the syntax to create a Unique Secondary Index:

Example

First create an Employee table with the following records, such as:

The following example creates USI on the Emp_Id column of the employee table.

The output of the Emp_Id hash will utilize the hash map to point to a specific AMP and that AMP will hold the secondary index subtable row for the secondary index value.

That means the subtable row will hold the base table Row-ID, and Teradata will then find the base row immediately.

Non-Unique Secondary Index (NUSI)

A Non-Unique Secondary Index (NUSI) allows duplicate values for the columns defined as NUSI.

A Non-Unique Secondary Index is usually specified to prevent full-table scans, in which every row of a table is read.

A 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.

When A NUSI is created, then the Teradata will immediately build a secondary index subtable on each AMP.

Each AMP will hold the secondary index values for their rows in the base table only. There can be up to 32 Secondary Indexes on a table.

Syntax

Below is the generic syntax to create a Non-Unique Secondary Index:

Example

The following example creates NUSI on the First_Name column of the employee table.

In the above example, each AMP holds the name column for all employee rows in the base table on their AMP (AMP local).

Each AMP Local name will have the Base Table Row-ID (pointer), so the AMP can retrieve it quickly if needed.

If an AMP contains duplicate first names, only one subtable row for that name is built with multiple Base Row-IDs.