Effects of Join Indexes - Advanced SQL Engine - Teradata Database

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.

Archive and recovery cannot be used on a join index itself. Archiving is permitted on a base table or database that has an associated join index defined. Before a restore of such a base table or database, you must drop the existing join index definition. Before using any such index again in the execution of queries, you must recreate the join index definition.

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.