The SKIP JOIN INDEX option has been added to the archive/restore/copy/build statements. The purpose of the SKIP JOIN INDEX option is to skip the Join Index handling in those respective statements. For archive, this removes the logic to read and archive the DDL for recreating the Join/Hash indexes. For restore/copy and build, this removes the logic to drop any existing Join/Hash indexes on the tables to be restored/copied or built on the target system before doing the restore/copy or build.
When SKIP JOIN INDEX is specified, the user must revert to manual handling of Join/Hash indexes that existed prior to Teradata Tools and Utilities 13.00.00. Particularly for restore/copy and build, the user must manually drop any existing Join/Hash indexes on the target system and manually recreate them after restoring, copying, or building the specified tables (see Dropping and Recreating a Join Index).
If the user does not drop the Join/Hash index when it is necessary, the Teradata Database will return the following error message when trying to restore the specified tables:
5468: Cannot delete database because either this database has a join or hash index in it or one of its tables has a join or hash index defined on it.
The following table describes use cases in which the SKIP JOIN INDEX option is designated within the archive, restore/copy, and build scripts. Additionally, the table indicates if the use case requires the user to manually drop the Join/Hash index before a restore/copy or build operation.
| Skip Joint Index Settings | |||||
|---|---|---|---|---|---|
| Use Case | Archive | Restore | Build | Drops Joint Index | Likely Use Case? |
| Case 1 | 0(1) | 0 | 0 | Teradata ARC | yes |
| Case 2 | 0 | 0 | 1 | User | no |
| Case 3 | 0 | 1(2) | 0 | User | no |
| Case 4 | 0 | 1 | 1 | User | no |
| Case 5 | 1 | 0 | 0 | Teradata ARC | yes |
| Case 6 | 1 | 0 | 1 | User | no |
| Case 7 | 1 | 1 | 0 | User | no |
| Case 8 | 1 | 1 | 1 | User | no |
| (1) A value of zero means the skip join index is not specified in the script. (2) A value of one means skip join index is specified in the script. |
|||||
- If specified on archive, then the archive file will not have any join index information.
- If specified on restore, then no join index information should restore.
- If specified on build, then no join index information should build.
The following are examples of archive, restore, and build scripts setting the skip join index option. Based on the table above, just remove the line that designates skip join index for cases where it is not needed.