Hash and Join Indexes | Teradata Vantage - 17.10 - Hash and Join Index Interactions With Other Teradata Systems and Features - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

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

Summary

The following table summarizes the Vantage 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
  • 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, B035-2411, Teradata® MultiLoad Reference, B035-2409, and Teradata® Parallel Transporter Reference, B035-2436 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, B035-2414, Teradata® Parallel Data Pump Reference, B035-3021, and Teradata® Parallel Transporter Reference, B035-2436 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. For more information, see Teradata® Parallel Data Pump Reference, B035-3021.