In addition to performing level-pendingop and level-one checks, level-two checking does the following:
- Determines whether row IDs on any given subtable are consistent with row IDs on other subtables by comparing lists of these IDs in those objects.
- Computes and compares the checksum of primary and fallback rows.
- Verifies that hash codes reflect correct row distribution in each subtable.
The following table summarizes the specific level-two checks. These checks are in addition to the general checks performed for all check levels. For more information on these general checks, see CheckTable General Checks.
|Object Checked||What CheckTable Does|
Level-two checks are performed on all primary and fallback data rows on all online AMPs. CheckTable does not check data on down AMPs. If an AMP is unavailable, the primary-to-fallback data check bypasses rows whose alternate (primary or fallback) copy would be expected to be on the unavailable AMP. However, CheckTable still scans all subtables to check for problems other than primary-to-fallback inconsistencies.
For checks that compare primary to fallback rows, CheckTable checks only tables that have fallback.
CheckTable does not check rows whose primary or fallback copies are inaccessible due to an unavailable AMP.
|reference index check||
If the reference index subtable is fallback, Teradata Database uses the row IDs of the reference index rows from the primary copy of the subtables.
SJIs are generated by Teradata Database for temporal tables that have temporal unique constraints. For more information on temporal tables and constraints, see Temporal Table Support.
Spool Space Requirements for Level Two Checks
Critical to level-two and level-three checking is the amount of spool space available on the system. The following formulas determine the amount of spool space required to perform these two levels of checking:
- RID = 32,020 * (# of rows / 3,200)
- SIS = (52 * # of rows) + (3,000,000 * # of AMPs)
To determine the number of rows, count the rows in the primary data table, which is the same as the primary index table. The following table shows the values and spool space required in the above formulas.
|The value …||Is the spool space required …|
|RID||for the data subtable.|
|SIS||by the largest secondary index subtable, whether it is unique or nonunique.|
Therefore, to perform a level-two check for a nonfallback table that involves the secondary index, SIS requires a total spool space equal to the following:RID + (SIS*2)
If the table contains fallbacks, then SIS requires a total spool space equal to the following:2 *(RID + (SIS*2))
If you specify PARALLEL mode, then the total required spool space equals the sum of the spool space required for each of the tables being checked.