17.10 - Rebuilding Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)
Table Rebuild can recover primary data, fallback data, or both primary and fallback data that is stored on an AMP. You can rebuild a single table, all tables in a specified database, or all tables on the AMP. As an option, the rebuild process can be limited to only those tables that have fallback protection.
On Teradata platforms optimized for fallback, all user tables have fallback. On platforms not optimized for fallback, you have the option to create tables with or without fallback.

The rebuild process uses fallback data to recover primary data, and primary data to recover fallback data on a specified AMP. The current, presumably corrupted contents of the table being rebuilt are deleted and replaced with the fallback or primary data, as appropriate. For tables with no fallback protection, Table Rebuild restores an empty table that retains only the table header information. Using the FALLBACK TABLES option ensures that only tables with fallback protection are rebuilt. See REBUILD AMP FALLBACK TABLES.

Tables that are marked down at the time they are rebuilt will remain down after the rebuild. To clear the down status, use the ALTER TABLE ... RESET DOWN statement after the table is rebuilt.

Global temporary tables, volatile tables, join indexes, and hash indexes cannot be rebuilt. For indexes that may be corrupted, drop the index and recreate it as needed.

Certain non-fallback system tables, such as DBC.DataBaseSpace and DBC.Acctg, store information that is unique to each AMP. These tables maintain information such as the current database space utilization, CPU utilization, and I/O statistics for the AMP. Instead these tables are updated by the system automatically after the rebuilt AMP is placed back on line.

The data for foreign tables is external to Vantage, so for these tables, only the table headers are rebuilt. For more information about foreign tables, see CREATE FOREIGN TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

If a database restart interrupts an ALL TABLES rebuild process, it can be restarted again, and will continue from where the rebuild process was interrupted. For more information, see RESTART REBUILD.

Locking During Rebuilds

Table Rebuild can apply one of three types of read locks while it is rebuilding tables:
  • Database-level read locks are placed on the database on the AMP used as the source of data for rebuilding the corrupted tables. This is the default type of lock that Table Rebuild applies.
  • Table-level read locks are place on the individual tables that are the sources of data for rebuilding the corrupted tables.
  • Rowrange-level read locks consist of selected groups of row-only locks. This type of lock allows concurrent updates of the tables being used as the sources of data for rebuilding the corrupted tables.

In the case of rebuilding primary data, these locks are placed on the AMPs containing the corresponding fallback data. In the case of rebuilding fallback data, the locks are placed on the AMPs containing the corresponding primary data.

Whether the database, table, or rowrange option is specified, Table Rebuild requests a read lock on all online AMPs which belong to the same cluster for the database or table being rebuilt. These AMPs contain the valid data that will be copied back to the AMPs being rebuilt. Rowrange-level locks are applied locally to the AMP that is being rebuilt, so that the valid data can be copied back to the rebuilt AMP.

For database and table rebuilds, processing begins after the lock is acquired. For rowrange rebuilds, the table-level lock is changed to a rowrange lock before processing begins.

If an AMP is online while all data or primary data is being rebuilt, an exclusive lock is placed on the database or table. If an AMP is online while fallback data is being rebuilt, a write lock is placed on the database or table. If the AMP is offline, a read lock is used in all cases.

If all tables on an AMP are being rebuilt, Table Rebuild attempts to set database-level read locks. If a database lock fails because of a conflict with another lock, that database is bypassed. Table Rebuild attempts to rebuild the database again after all accessible databases and tables are processed. Table Rebuild successively runs through the list of bypassed databases to attempt to get a lock. If the bypass list contains a single database, or Table Rebuild has tried the list of bypassed databases ten times, rebuild processing stops and waits until a lock can be acquired.

If Table Rebuild is running in the background, a message is displayed on the system console identifying the database and stating that the utility is waiting for a lock.