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. |
|
| FastLoad | ||
| Teradata Parallel Transporter LOAD and UPDATE operators | ||
|
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.