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