Archives of the TeradataForum
Message Posted: Thu, 02 Jun 2005 @ 08:17:51 GMT
| Subj: | | Re: Hash Indexes |
| |
| From: | | Victor Sokovin |
| | What is advantages of Hash Indexes over other index | |
I think they can be compared with Secondary Indexes or single-column Join Indexes. The main advantage I can think of is that the HI allow a
user to define an alternative Primary Index by specifying columns in the BY clause. This helps the user with tuning the physical allocation of
data across AMPs within the HI subtable, which in its turn can be beneficial for certain queries covered by the HI.
The disadvantage of HI is that they can make DBA's life more difficult. Just as JI, they might not behave well during backup / restore
procedures and even influence the quality of the base table backups.
I see HI as a kind of luxury tool for advanced users working on dependent data marts or tables in their private databases. They would typically
create an HI to speed up certain queries they anticipate running multiple times over a short period of time. HI might be dropped as soon as the
users are no longer interested in that particular type of queries. Then some other HI would be created, etc.
Regards,
Victor
| 
