HASH INDEX IN TERADATA

Share via:

Dear Readers ,

In this article, we will see Hash indexes in Teradata.

Hash indexes are file structures that share properties in common with both single-table join indexes and secondary indexes.

From an architectural perspective, the incorporation of auxiliary structures as a transparently embedded element of the hash index column set is what most distinctly distinguishes hash indexes from single-table join indexes. These auxiliary structures are components of the base table, and are added to the hash index definition by default if they are not explicitly declared by the CREATE HASH INDEX column set definition. Because it is not clear what the default auxiliary structures Teradata Database uses when it create a hash index, you should always consider creating an equivalent single-table join index in preference to a hash index. Also, multivalue compression from the base table may be carried over to a join index, but it is not carried over to a hash index.

If the columns you specify for the hash index column set duplicate the default auxiliary structure columns, then those columns are not added redundantly. The auxiliary structures provide indexed access to base table rows.

If you do not specify a partition key explicitly with the BY clause of the CREATE HASH INDEX statement, then the system adds this auxiliary pointer data to the hash index rows automatically and then uses it to partition them.

Difference between Hash Index and Single table join Index:

  • The main external difference between the Hash Index and Single table join Index is in the syntax to create them. It is easy to create a hash index than a functionally comparable single table join index.
  • Hash index must have a primary index, but the single table join index may or may not have.
  • We cannot define a partitioned primary index on a hash index, but can define on single table join index.
  • We cannot specify Non-Unique Secondary Index, aggregate function, trigger on Hash Index, but all these we can define on single table join index.

The following list summarizes the similarities of hash and single-table join indexes.

  • The primary function of both is to improve query performance.
  • Both are maintained automatically by the system when the relevant columns of their base table are updated by a        DELETE, INSERT, or UPDATE statement.
  • Both can be the object of any of the following SQL statements.
  • COLLECT STATISTICS (Optimizer Form)
  • DROP STATISTICS
  • HELP INDEX
  • SHOW HASH INDEX
  • Both receive their space allocation from permanent space and are stored in distinct tables.
  • Both can be hash- or value-ordered. You must drop and rebuild all value-ordered (but not hash-ordered) hash and         join indexes after you run the Reconfig utility.
  • Both can be row compressed.
  • Both can be FALLBACK protected.
  • Both can be used to transform a complex expression into a simple index column. This transformation permits you       to collect statistics on the expression, which the Optimizer can then use to make single-table cardinality estimates         for a matching complex column predicate specified on the base table and for mapping a query expression that is             identical to an expression defined in the join index, but is found within a non-matching predicate .
  • Neither can be queried or directly updated.
  • A hash index cannot have a partitioned primary index, but a single-table join index can.
  • A hash index must have a primary index, but a single-table join index can be created with or without a primary           index if it is column-partitioned.
  • A hash index cannot be column-partitioned, but a single-table join index can be column-partitioned.
  • Neither can be used to partially cover a query that contains a TOP n or TOP m PERCENT clause.
  • Neither can be implemented with row compression if they specify a UDT in their select list because both create an             internal column1 and column2 index when compressed.
  • Neither can be defined using the system-derived PARTITION column.
  • Both share the same restrictions for use with the MultiLoad, FastLoad, and Archive/Recovery utilities.

Summary of Hash Index Functions

A hash index always has at least one of the following functions.

Replicates all, or a vertical subset, of a single base table and partitions its rows with a user-specified partition key column set, such as a foreign key column to facilitate joins of very large tables by hashing them to the same AMP.

Provides an access path to base table rows to complete partial covers.

Example 1:-

Example table:

Hash Index Syntax:

From the above example characteristics of the hash index can be noted as:

No need to include EMP_ID column as it is primary index for the base table,so it will be automatically included in the hash index.

Hash index sub-table rows will be AMP local to the base table and row hash sequence are in hash sequence based on base table primary index.

Example 2:

In this example-

Same as before, hash index sub-table will be AMP local as the values will be redistributed based on base table primary index value.

But in this case hash index rows will be stored in EMP_SSN_NO sequence rather than row hash sequence.

This type of hash index is useful for range queries.

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment