Query Performance

Pankaj Mehta

Indexing Structures

Teradata indexes are subtables that contain base table index column values and RowIDs which point to the rows in the base table having that value. The rows of these index subtables are shared across all VPROCs, but how this subtable partitioning takes place (globally or locally) will depend on whether this is a unique secondary index (USI) or non-unique secondary index (NUSI).

Primary Indexes

Teradata’s primary index is a direct hash. This is extremely fast for most operations. There are two levels of indexing the hash codes. The first level is locked in memory. This guarantees that the worst case for access to a single row is 2 I/Os. I/O caching gives a high probability that the second level index is in cache and some chance that the data block is in memory as well. Table storage mechanisms guarantee that as data is inserted or changed, no increased overhead is incurred (no hash chains, block extensions, ...). Background tasks insure that fragmentation is cleaned up on an as needed basis.

Advantages

Fast access for single row operations
The hashed structure is sorted by hash and optimized for use by the Hash Merge Join algorithm.
No preparation of the table is necessary, if the table is joined via its primary index.
The join algorithm reads it directly.

The value contained in these indexed columns is used by Teradata to determine the VPROC which will own the data as well as a logical storage location within the VPROC’s associated disk space. Most often the physical primary index will be the same column as the logical primary key.

Secondary Indexes

Secondary indexes are used to provide faster set selection. Teradata offers either unique or non-unique index structures and can use dynamically generated intersection of multiple indexes, both primary and secondary.
If unique secondary indexes are stored in a hash index structure, the secondary index value is used to determine the Virtual AMP and the storage location. If, non-unique secondary indexes are stored as local hash structures, the primary index value is used to determine the Virtual AMP, the secondary index value is used to determine the storage location.

Unique Secondary Indexes (USI)
If an index is defined as unique it will function in Teradata as a global index. The value being indexed will be hashed, and the resulting hash bucket will determine which VPROC will hold the index information for that value. A given VPROC’s portion of the USI subtable will hold rows that point to base table rows that reside on any of VPROC’s across the system.
To access a base table row using a USI, the key value is hashed, the index entry will be located within the VPROC identified in the resulting hash bucket, and the RowID of the base table will be used to locate the base table row’s VPROC and hashID. This operation will usually involve two different VPROCs, one that holds the index entry, the second that holds the associated base table row, as shown in the following illustration.

Non-Unique Secondary Indexes (NUSI)
Non-Unique Secondary Indexes in Teradata are always local structures and do not involve hashing. That is, a given VPROC’s NUSI subtables only contain entries for base table rows owned by that VPROC. Each VPROC will have a secondary index row for each distinct secondary index value found within its subset of base table rows. The rowIDs point to base table rows on this VPROC only.