17.10 - Effects of Join Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Fundamentals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)
  • Load Utilities

    MultiLoad and FastLoad utilities cannot be used to load or unload data into base tables that have a join index defined on them because join indexes are not maintained during the execution of these utilities. If an error occurs because of a join index, take these steps:

    • Ensure that any queries that use the join index are not running.
    • Drop the join index. (The system defers completion of this step until there are no more queries running that use the join index.)
    • Load the data into the base table.
    • Recreate the join index.

      The TPump utility, which performs standard SQL row inserts and updates, can be used to load or unload data into base tables with join indexes because it properly maintains join indexes during execution. However, in some cases, performance may improve by dropping join indexes on the table prior to the load and recreating them after the load.

  • Permanent Journal Recovery

    Using a permanent journal to recover a base table (that is, ROLLBACK or ROLLFORWARD) with an associated join index defined is permitted. The join index is not automatically rebuilt during the recovery process. Instead, it is marked as nonvalid and it must be dropped and recreated before it can be used again in the execution of queries.