Performance between Hash Indexes & Join indexes. Also similarities and differences between Hash Indexes AND Join Indexes in Teradata

Teradata: Hash Indexes Vs Join Indexes

• Hash Indexes are mostly compared with Single table Join Indexes.
  
• As Hash Indexes and Join Indexes are used for improving performance of queries, users tend to compare the performance of both.
  
• In the matter of fact both Hash Index and Join Index give similar performance.
  
• This makes users to compare other characteristics of Hash Index & Single table join indexes to understand which one is better.
  
• This tutorial will going to cover many similarities & differences between Hash Indexes and Join Indexes.

Note: Teradata suggest to use Single-Table Join Indexes instead of Hash Indexes.


Similarities between Hash Indexes & Join Indexes

• Both Join Indexes & Hash Indexes are created to improve query performance.
• Both Join Indexes & Hash Indexes uses permanent space to store their data.
• Both Join Indexes & Hash Indexes can be hash-ordered or value-ordered.
• Both Join Indexes & Hash Indexes can be FALLBACK protected.
• Both Join Indexes & Hash Indexes can have COLLECT STATISTICS defined on them.
• Teradata utilities like MultiLoad, Teradata Parallel Transporter,FastLoad, and Archive/Recovery utilities can be used on tables with Join Indexes & Hash Indexes.
• Neither Join Indexes nor Hash Indexes can be used to partially cover a query that contains a TOP n or TOP m PERCENT clause.
• Both Value-ordered Join Indexes nor Value-ordered Hash Indexes must drop and rebuild after running the Reconfig utility(DBA stuff).

Differences between Hash Indexes & Join Indexes based on various parameters

	Hash Indexes	Join Indexes
Primary Index from the base table	Hash index include the base table’s Primary Index value if no primary index is specified.	Join index does not include base table’s Primary Index if no primary index is specified
Multivalue compression from the base table	Multivalue compression from the base table is not carried over to a hash index	Multivalue compression from the base table may be carried over to a join index
Index row compression from the base table	Transparently added from base table by the system for hash index	Not added by system and must be specified in the create Join Index request
Triggers	Hash index cannot be defined on table with triggers and vice versa (trigger can not be defined on table with Hash Index)	No such limitations in case of Join index, both can be defined simultaneously
NOPI Table	Hash Index are not supported on these tables	Join index are supported on these tables
Column partitioned table	Hash Index are not supported on these tables	Join index are supported on these tables
Column partition	Hash Index can not be created with Column Partition	Join Index can be created with Column Partition
PPI	PPI can not be defined on Hash Indexes	PPI can be defined on join Indexes
Number of Tables	Hash Index can defined on a single table	Join Index can defined on a single table as well as multiple tables

Visit Teradata Hash Index and Teradata Join Index if you want to learn more about hash indexes & join indexes respectively.