Level-three checking provides the most detailed check and requires more system resources than any of the other checking levels. Because of the cost in system resources, use this checking level rarely and only for specific diagnostic purposes.
In addition to checks that are unique to this level, level-three checking includes most of the same checks that are performed at lower check levels. While no additional level-three checks are performed on large objects (LOBs), a level-three check will automatically perform level-two checking of LOBs.
If an AMP is unavailable and the table was not defined with fallback, CheckTable bypasses all USI checks.
If an AMP is unavailable and the table is fallback, then CheckTable uses the fallback copies of index and data subtables on the available AMP in place of the primary copies on the unavailable AMP.
The following table summarizes the specific level-three 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 |
---|---|
data subtable |
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 multilevel partitioned primary indexes, the external partition number in the error message is the number resulting from the combined partition expression. For more information on partitioned primary indexes and on partitioning expressions, see Teradata Vantage™ - Database Design, B035-1094. To correct the partitioning of rows for a table, use the ALTER TABLE statement with the REVALIDATE PRIMARY INDEX WITH DELETE or INSERT option. For more information on ALTER TABLE, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. |
LOB subtable |
|
USIs |
Checking for duplicates on MULTISET tables with unique indexes can be expensive with respect to performance. When there are many rows in such a table that have the same hash value, this check can take a long time. This would be the case for a table with a highly nonunique PI. For each hash value, CheckTable scans through the entire hash, row by row, and checks for duplicates on the unique index. Since NoPI and PA tables generally have only one hash value per AMP, USI checks can be slow.
|
NUSIs |
|
reference index subtables |
|
SJIs |
|
As data subtables are scanned, the internal partition number of each table row is verified if the table has row partitioning. If a scanned internal partition number does not match the number calculated using the combined partitioning expression, CheckTable displays an error message. If the table is column partitioned, this check is not performed.