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:
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 the 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 sequences 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 example-
- 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.
- 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.