Hi, Hash Indexes are base tables that cannot be accessesd directly by a query. The differences between hash and single table join indexes are:
1)Hash indexes cannot have secondary index,but single table join indexes can have secondary index. 2)Hash index column list cannot have aggreagete functions,but single table join index supports aggreagete function with column list.
Hi, Teradata manuals say that Hash Indexes are highly recommended for decision support systems for best performance in ad-hoc queries and not recommended for OLTP systems. The optimizer includes a hash index in a query plan when the index covers all or part of a join query thus eliminating the need to redistribute the rows. However hash index carries more burden than a secondary index. And the storage of a hash index is similar to a base table storage except that hash indexes can be compressed.
Discussion abt when to use a hash index instead of a secondary or join index should be continued in the forum.