16.10 - Hash and Join Index Interactions With Other Teradata Database Systems and Features - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

This topic describes how hash and join indexes interact with the following Teradata Database systems and features.

Summary

The following table summarizes the Teradata Database features that do not support hash or join indexes.

Feature Reason Not Supported Recommended Workaround
Triggers Triggers are handled by the Resolver, while hash indexes are handled by the Optimizer. Do not define triggers and hash indexes on the same base tables.
Permanent journal recovery Recovery process does not rebuild hash and join indexes. Rebuild hash and join indexes after the permanent journal recovery completes.
MultiLoad Utilities do not maintain hash and join indexes.
  1. Ensure that no queries are running against tables that use the hash or join index to be dropped.
  2. Drop hash and join indexes before loading or restoring the base tables.
  3. Recreate hash and join indexes after loading or restoring the base tables.
FastLoad
Teradata Parallel Transporter LOAD and UPDATE operators
Archive/Recovery
  • NoPI tables
  • Column-partitioned tables
Hash indexes automatically add the primary index of their underlying base tables to their definition, and NoPI tables have no primary index. None.

Triggers

You cannot define triggers and hash indexes on the same table; however, you can define triggers and join indexes on the same tab

Whichever feature is defined first for a table blocks the other from being created and returns an error message to the requestor.

Permanent Journal Recovery

You can use ROLLBACK or ROLLFORWARD utility commands to recover base tables with hash or join indexes defined on them; however, the indexes are not rebuilt during the recovery process.

Instead, any such hash or join index is marked as non-valid, and you must drop and recreate it before the Optimizer can use it again in a query plan.

When a hash or join index has been marked not valid, the SHOW HASH INDEX and SHOW JOIN INDEX statements display a special status message to inform you that the index has been so marked.

Load Utilities

You cannot use FastLoad, MultiLoad, or the Teradata Parallel Transporter operators LOAD and UPDATE to load data into base tables that have hash or join indexes because those indexes are not maintained during the execution of these utilities (see Teradata FastLoad Reference, Teradata MultiLoad Reference, and Teradata Parallel Transporter Reference for details).

If you attempt to load data into base tables with hash or join indexes using these utilities, an error message returns and the load does not continue.

To load data into hash- or join-indexed base table, you must drop all defined hash or join indexes before you can run FastLoad, MultiLoad, or the Teradata Parallel Transporter operators LOAD and UPDATE.

Load utilities like BTEQ, Teradata Parallel Data Pump, and the Teradata Parallel Transporter operators INSERT and STREAM, which perform standard SQL row inserts and updates, are supported for hash- and join-indexed tables (see Basic Teradata Query Reference, Teradata Parallel Data Pump Reference, and Teradata Parallel Transporter Reference for details).

You cannot drop a hash join or index to enable batch data loading by utilities such as MultiLoad and FastLoad as long as queries are running that access that index. Each such query places a lock on the index while it is running, so it blocks the completion of any DROP JOIN INDEX or DROP HASH INDEX transactions until the lock is removed.

Furthermore, as long as a DROP JOIN INDEX or DROP HASH INDEX transaction is running, batch data loading jobs against the underlying tables of the index cannot begin processing because of the EXCLUSIVE locks DROP JOIN INDEX and DROP HASH INDEX place on the base table set that defines them.

Teradata Parallel Data Pump

You can use the Teradata Parallel Data Pump utility, which performs standard SQL row inserts and updates, to load data into base tables that have a hash or join index defined on them because those indexes are properly maintained during its execution. See Teradata Parallel Data Pump Reference for details.

Archive and Recovery

You cannot use the Teradata Archive/Recovery utility to archive or restore hash and join indexes, though archiving is supported for a base table or database that has associated hash or join indexes defined on it.

Before you can restore such a base table or database, you must drop the hash and join indexes that are associated with it, and then recreate the dropped indexes after the recovery operation completes. Otherwise, the Optimizer cannot use those indexes for a query plan.

See Teradata Archive/Recovery Utility Reference for details.