Hash vs Join index


Hash vs Join index

What are the differences between a hash index and a single table join index?
When should I choose one over the other?


Re: Hash vs Join index

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.


Re: Hash vs Join index

So, there is nothing I can do with a hash index that I can't also accompish with a join index.

A join index can do more (aggreate functions, secondary indexes).

Neither can be accessed directly.

So, why would I ever use a ahs index instead of a join index?

Re: Hash vs Join index

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.