Secondary Index provides an alternate path to the rows of a table.Teradata achieve alternate path by creating sub tables for each secondary index.

Teradata Secondary Index

Secondary Index provides an alternate path to the rows of a table.Teradata achieve alternate path by creating sub tables for each secondary index.

Secondary Index characteristics:

• A table can have up to 32 secondary indexes (including join/hash indexes).
• Maximum of 64 columns can be added in a single secondary index for a table.
• Does not affect data distribution for table and can be added or dropped as needed.
• Improves table access performance by providing alternative path.
• Secondary index on multiple columns is less usable.Index would be used only when all columns are used in the WHERE clause.
• Adds overhead, both in terms of disk space and maintenance.

There are two types of Secondary Indexes

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

➠ Unique Secondary Index (USI): It means that the values for the selected column must be unique.

• USI access is atmost a two-AMP operation and will return atmost single row.

Syntax/Example 1:

CREATE UNIQUE INDEX [index_name](col1,col2) 
       ON [databasename.table_name];

➠ Non-Unique Secondary Index (NUSI): It means that the values for the selected column can have duplicates.

• NUSI sub-tables are local to the AMP corresponding to its base table and by default, are sorted in row hash of the secondary index column.
• Always Collect Statistics on all NUSI indexes.
• NUSI queries are All-AMP operations but not Full Table Scans.

Syntax/Example 2:

CREATE INDEX [index_name](col1,col2) 
       ON [databasename.table_name];

CREATE INDEX Employee_table_nusi(emp_name,manager_id,salary,job_title) 
      ON tutorial_db.employee;

➠ Value-Ordered NUSI

• It is called a Value Ordered NUSI because subtable is sorted numerically based on actual column value instead being sorted by Secondary Index Value hash.
• Works best for the range queries.
• Can be defined only on 'numeric' & 'date' data types.

Syntax/Example 3:

CREATE INDEX [index_name] ([date_or_number_column])
     ORDER BY VALUES ([date_or_number_column])
     ON [databasename.table_name];

CREATE INDEX Employee_table_index (dept_no)
     ORDER BY VALUES (dept_no)
     ON tutorial_db.employee;


Syntax/Example 4: Sample Range Query

SELECT * FROM Employee WHERE Dept_no BETWEEN 100 AND 200;

➠ Covered Query: When all columns in a query can be satisfied by a multi column secondary index subtable it is called a Covered Query.

Example 4: All the column for below query is satisfied by Employee_table_nusi Index.

SELECT
  emp_name,
  manager_id,
  salary,
  job_title
FROM tutorial_db.employee

➠ Drop Secondary Index

• Dropping named secondary indexes

  
  DROP INDEX indexname ON tutorial_db.employee;
  

• Dropping unnamed secondary indexes

  
  DROP INDEX(co) ON tutorial_db.employee;