Hash Index in Teradata

Hash index in Teradata has been designed to improve the performance of Teradata query in a way similar to the Single Table Join Index, which restricts parsing engine not to access or redistribute the base table. It may act like a secondary index by providing an alternative access path for the base table.

Difference between Hash Index and Single table join Index:-

• The main external difference between the Hash Index and Single table join Index is in the syntax to create them. It is easy to create a hash index than a functionally comparable single table join index.
• Hash index must have a primary index, but the single table join index may or may not have.
• We cannot define a partitioned primary index on a hash index but can define it on a single‑table join index.
• We cannot specify Non-Unique Secondary Index, aggregate function, trigger on Hash Index, but all these we can define on single‑table join index.

Note:- Both hash index and single-table join index are defined to improve the query performance. A hash index is a restricted form of a single-table join index so performance wise both should be the same. Consider below examples of the hash index to understand the creation process:

Example 1:-

Example table: 

CREATE BASE TABLE EMPLOYEE (
    EMP_ID INTERGER NOT NULL,
    EMP_SSN_NO INTEGER NOT NULL,
    EMP_NAME VARCHAR(100),
    MANAGER_ID INTEGER NOT NULL,
    MANAGER_NAME VARCHAR(100),
    DEPT VARCHAR(30))
UNIQUE PRIMARY INDEX(EMP_ID);  

Join Index Syntax: 

CREATE HASH INDEX EMP_HI (
    EMP_SSN_NO,
    DEPT 
)
ON EMPLOYEE;

OR

CREATE HASH INDEX EMP_HI (
    EMP_ID,
    EMP_SSN_NO,
    DEPT 
)
ON EMPLOYEE
BY (EMP_ID)
ORDER BY HASH (EMP_ID);

From the above example characteristics of the hash index can be noted as:

1. No need to include EMP_ID column as it is the primary index for the base table, so it will be automatically included in the hash index.
2. Hash index sub-table rows will be AMP local to the base table and row hash sequences are in hash sequence based on base table primary index.

Example 2: 

CREATE HASH INDEX EMP_HI (
  EMP_ID,
  EMP_SSN_NO,
  DEPT 
)
ON EMPLOYEE
BY (EMP_ID)
ORDER BY VALUES (EMP_SSN_NO);

In this example-

1. Same as before, the hash index sub-table will be AMP local as the values will be redistributed based on the base table primary index value.
2. But in this case, hash index rows will be stored in EMP_SSN_NO sequence rather than row hash sequence.
3. This type of hash index is useful for range queries.