Dropping and Recreating a Join Index - TARA/ABU

1. Extract the join index definition by executing a SHOW JOIN INDEX command and by saving the join index definition.
2. Drop the join index.
3. Restore the database or the table.
4. Recreate the join index by executing a CREATE JOIN INDEX command with the saved join index definition.
5. Collect the new statistics.

Teradata Tools and Utilities 13.00.00 and later

Beginning with Teradata Tools and Utilities 13.00.00, join and hash indexes can be archived and restored if these requirements are met:

• The site must use Teradata Tools and Utilities 13.00.00 and later.
• The site must use Teradata Database 13.00.00 and later
• The user must have SELECT rights on the DBC.TablesV and DBC.JoinIndicesV views.

Join and hash indexes cannot be restored from a pre-13.00.00 archive file because the indexes were not archived prior to 13.00.00.

When restoring a user table, any join and hash indexes defined in that table are automatically dropped prior to restoring the table. After the table is restored, the join and hash indexes are automatically recreated. The indexes are rebuilt from the archived definition if they do not currently exist on the system. If an archived join or hash index currently exists on the table, the existing definition is used to rebuild the join or hash index.

Individual join or hash index definitions can be restored by specifying the index name in the list of objects to restore.

When restoring a user table that has a join or hash index defined on it (or database containing such a table), the name of the user table or database must be specified in the RESTORE statement. If ALL FROM ARCHIVE is used to restore the table or database containing the user table with a join index, the job is aborted with a DBS error (error 5467 for a table and error 5468 for a database). This occurs because Teradata ARC is not given the name of the table or the database being restored and is therefore, unable to drop the associated join or hash index before restoring the table or database from the archive file. When using the ALL FROM ARCHIVE option, use the pre-TTU 13.0 procedure to drop manually all of the join and hash indexes associated with the databases and tables being restored from the archive file before running the job.