The hash index in Teradata has been designed to improve the performance of Teradata query in the way similar to the Single Table Join Index, which restricts parsing engine not to access or redistribute the base table. It may act like 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 on 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 single-table join index so performance wise both should be same. Consider below examples of hash index to understand the creation process:
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:
- No need to include EMP_ID column as it is primary index for the base table,so it will be automatically included in the hash index.
- Hash index sub-table rows will be AMP local to the base table and row hash sequence are in hash sequence based on base table primary index.
CREATE HASH INDEX EMP_HI ( EMP_ID, EMP_SSN_NO, DEPT ) ON EMPLOYEE BY (EMP_ID) ORDER BY VALUES (EMP_SSN_NO);
In this eample-
- Same as before, hash index sub-table will be AMP local as the values will be redistributed based on base table primary index value.
- But in this case hash index rows will be stored in EMP_SSN_NO sequence rather than row hash sequence.
- This type of hash index is useful for range queries.