Interview Questions   Tutorials   Discussions   Programs   

Teradata - What is Hash Indexes in the teradata ?

asked vishnoiprem August 17, 2014 06:08 AM  

What is Hash Indexes in the teradata ?


1 Answers

answered By vishnoiprem   0  
Hash indexes are similar to single-table simple join indexes in that they are used for
denormalizing a single table by storing rows in a separate physical object from the base
table. Hash indexes are limited to a SINGLE table and are beneficial in improving

Like join indexes, Hash index rows are stored in a different object than the base table
rows. This allows for faster access without needing access to the underlying rows. Also
like their relative, they function as an alternative to the base rows instead of providing a
different access path.

Like join indexes, hash indexes are known as “covered queries” because they are utilized
when they contain all the columns requested by the query. If all of the columns are
covered, the optimizer will usually choose to access the rows via the hash index instead
of the base table rows. In a situation where the hash index partially covers the query, the
optimizer may use the Row ID included with the Hash Index to access the other columns
in the data row.
Join indexes and hash indexes are both transparently maintained by Teradata. When the
base table changes, the Hash Index table is automatically updated. This automatic task is
referred to as an update burden. Being that Hash Indexes are strikingly similar in
functionality to secondary indexes, they should be carefully considered because they
carry this update burden.

Hash indexes however, can offer marginal performance gains over a secondary index
when certain columns are frequently needed from one table to join with many others. In
these situations, the Hash index partially covers the query, but fully covers the rows from
the base table, effectively eliminating any need to access the base table.
Hash Indexes:
• Can be ordered by hash or by values to facilitate range queries.
• Automatically has the Row ID of the base table row included in the Hash Index
which the RDBMS software can use to access columns not included in a “covered
   add comment

Your answer

Join with account you already have



Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!