Teradata Hash Index provides better performance by providing an alternate access path

Teradata Hash Index

Hash Index provides better performance by providing an alternate access path

Hash Index characteristics:
Hash Index drawbacks:
When to use a Hash Index:
Below points briefly describe how Hash Index is different from Join Index, visit Hash Index Vs Join Index to get more detail.

Note: Unlike Join Index & tables,columns used for data distribution is specified by 'By' clause and not by 'Primary Index'. Remember that the columns used for data distribution must be part of the columns which make up the Hash Index. If no 'By' clause is mentioned then hash index will distribute data considering base table's primary index


Example 1: Normal Hash Index
CREATE HASH INDEX hash_index_name(manager_id,salary)
ON tutorial_db.employee;

Example 2: Hash Ordered Hash Index different PI and hash column test
CREATE HASH INDEX employee_hash_index_ordered (manager_id,salary) ON tutorial_db.employee
BY (manager_id) ORDER BY HASH (salary);
Output: *** Failure 5566 Error in Hash Index DDL, Primary index and order by hash columns should be the same.

Example 3: Hash Ordered Hash Index
CREATE HASH INDEX employee_hash_index_ordered (manager_id,salary) ON tutorial_db.employee
BY (manager_id) ORDER BY HASH (manager_id);

Example 4: Value Ordered Hash Index 4 byte test
CREATE HASH INDEX employee_hash_index_ordered (manager_id,salary) ON tutorial_db.employee
BY (manager_id) ORDER BY (salary);
Output: *** Failure 5566 Error in Hash Index DDL, Order by column is non-numeric, or is more than 4 bytes.

Example 5: Value Ordered Hash Index
CREATE HASH INDEX employee_hash_index_ordered (manager_id,emp_no) ON tutorial_db.employee
BY (manager_id) ORDER BY (emp_no);