How to Create or Drop the Secondary Index in Teradata

Secondary index in Teradata

Secondary index is an alternate path to the rows of a table in Teradata. It is used to
improve performance by allowing the user to avoid scanning the entire table during a query.Unlike a primary index, it has no influence on the way rows are distributed among AMPs. It is used to avoid the full table scan while accessing the data in the table.

  • Secondary index can be created after the table is created.
  • It may be dropped at any time.
  • It does not affect table row distribution.It is chosen to improve the access performance.
  • It can defined as Unique Secondary Index (USI) or Non Unique Secondary Index(NUSI)

Syntax to create the Secondary index in Teradata

Example to Create the Secondary index in Teradata

Lets create the table with few primary index values and then will create secondary index for the same table.

Create Non Unique Secondary index in Teradata

The customer transaction table is created with the primary indexes of customer id and Transaction date columns. If the data volume is high in the table, It will affect the query performance. In order to improve performance, we can create the secondary index for the Location id column.

We named the secondary index as Customer_Loc_INDX for the column Location_id. Since we didn’t mention the “UNIQUE” in the index creation, It will treated as Non Unique secondary Index.

Create Unique Secondary index in Teradata

In this example, we are going to create the Unique secondary index for the customer table. The column Location_id is used for the secondary index and Secondary index is named as “Customer_Loc_IDX” as below.

Lets run the Show table statement on this table to see the table structure.

The below create table statement shows the Secondary index name “Customer_Loc_IDX” with the column name.

Drop the Secondary index in Teradata

We can drop the secondary index at any time if it is not required. Either we can use the index name or mention the column name to drop it from the table.Lets see the syntax to drop the secondary index in Teradata.

Example to drop the secondary index in Teradata

  • If we create the secondary index with the name, we can mention the index name to drop it from the table.
  • If we didn’t specify the secondary index name, we need to mention the secondary index column name to drop it from the table.

Lets see the example to drop the secondary index which doesn’t have name.

Recommended Articles