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