Hash Indexes (Teradata)

Hash indexes are designed to improve query performance like join indexes, but may in addition enable you to avoid accessing the base table. Hash indexes are supported for Teradata v12 and higher. PowerDesigner models hash indexes as extended objects with a stereotype of <<HashIndex>>.

Creating a Hash Index

You can create a hash index in any of the following ways:

Select Model > Hash Indexes to access the List of Hash Indexes, and click the Add a Row tool.
Right-click the model (or a package) in the Browser, and select New > Hash Index.

Hash Index Properties

You can modify an object's properties from its property sheet. To open a hash index property sheet, double-click its diagram symbol or its Browser entry in the Hash Indexes folder.

The following extended attributes are available on the General tab:

Name	Description
Table	Specifies the base table on which the hash index is defined. Scripting name: Table
Database	Specifies the name of the database containing the base table. By default the same as the database in which the hash index is created. Scripting name: Owner
Fallback	Specifies that the hash index uses fallback protection. Scripting name: Fallback
Checksum	Enables a table-specific disk I/O integrity checksum level. The checksum setting applies to primary data rows, fallback data rows, and all secondary index rows for the index. Scripting name: Checksum

The following extended attributes are available on the Teradata tab:

Name	Description
Columns	Specifies the base table columns on which the hash index is defined Scripting name: Columns
Distributed columns	Specifies an optional, explicitly specified column set on which the hash index rows are distributed across the AMPs. This is a subset of index column list. Scripting name: ByColumns
Order by columns	Specifies the row ordering on each AMP, which must be either value-ordered or hash-ordered. Scripting name: OrderByColumns
Ordering type	[if Order by columns are specified] Specifies the ordering type of the ORDER BY column. Scripting name: OrderByType