Physical Modeling › Teradata Physical Property Editors › Define Teradata Hash Indexes

Define Teradata Hash Indexes

You can use the Teradata Table Hash Index Editor to create a set of new hash indexes on an existing table in a Teradata physical model. The hash index provides an index structure that can be hash-distributed to Access Module Processors (AMPs). The index contains a row identifier that provides transparent access to the base table, which improves query performance by covering columns in a query so that the base table does not need to be accessed, or serves as an alternate access method to the base table in a join or retrieval operation.

To define a Teradata hash index

1. Expand a Table in the Model Explorer. Expand Hash Indexes, right-click a Hash Index, and select Properties.

   The Teradata Table Hash Index Editor opens.

2. Select a hash index and work with the following options:

   Note: Click New on the toolbar to create a new hash index. Use the Enter filter text text box to filter a very large list of indexes to quickly locate the one you want to work with.

   Table

   Specifies the table you want to create a Hash index for. Select a table from the drop-down list.

   Name

   Specifies the name of the hash index.

   Database

   Defines the database or user.

   Generate

   Generates DDL for this object during Forward Engineering.

3. Complete the following fields on the General tab:

   Index Members

   Specifies the columns for the table that have the index and are key group members. You can select any of the columns to be a key group member. The columns can be sorted.

4. Click the Options tab and work with the following options:

   Fallback Protection

   Specifies the type of fallback protection for the database. Select from the drop-down list. Valid values are: No Fallback or Fallback.

   Checksum

   Specifies the integrity checking level for disk I/O. Select from the drop-down list. Valid values are: All, Default, Low, Medium, High, or None. This property only displays if the index is a table index.

   Order By

   Specifies how the rows are ordered. Select from the drop-down list. Valid values are: Column, Hash, or Value.

5. Click the Ordering tab, then select the check box next to the column name in the Hash by Column or Order by Column grid.

   Hash by Columns

   Displays the valid columns that you can select to participate in the hash. This property only displays for a table index.

   Order by Columns

   Displays the valid columns that you can select to participate in the ordering.

   Use the up and down arrow buttons to order the columns in each grid.

6. Work with the other tabs in the editor to access additional design features, for example:
   • Specify statistics options on the Statistics tab.
   • Associate permissions with the selected hash index on the Permission tab.
7. (Optional) Click the Comment tab and enter any comments you want associated with the hash index.
8. (Optional) Click the UDP tab to work with user-defined properties for the hash index.
9. (Optional) Click the Notes tab to view history information and view or edit user notes.
10. Click Close.

    The Teradata Table Hash Index Editor closes.

More information:

Add Permissions in a Teradata Property Editor

Add a Comment in a Property Editor

Add a UDP in a Physical Property Editor