17.10 - Level Three Checks - 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)

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
  • for tables that have fallback, compares each physical row in the primary data subtable with the corresponding physical row in the fallback data subtable. Makes a byte-by-byte comparison at all levels.
  • checks for duplicate, out-of-order, or incorrectly distributed rows, and duplicate unique primary keys.
  • verifies row hashes.
  • verifies the field size of each field in the data row with the defined maximum field size for the corresponding column. The field sizes are checked in the primary data row, not the fallback data row. Any field size error in the fallback data rows is discovered in the byte-by-byte comparison between the primary and fallback data rows.
  • for row-partitioned tables, verifies the internal partition numbers. This check is not performed for column-partitioned tables.
  • for column-partitioned tables, the physical data row IDs of each container row is verified to be greater than the physical row IDs of the previous container row, if any, plus the number of logical row IDs minus one represented by that container row. Container rows are verified for the integrity of their format.
  • for temporal tables, checks for value equivalent rows with overlapping temporal time values.
  • If the COMPRESSCHECK option was specified, CheckTable verifies compressed column data. For example, for multi-value compressed columns, CheckTable compares the compress list in the table header to the column values in each row to ensure the appropriate values were compressed.

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
  • checks for duplicate row IDs, out-of-order row IDs, and incorrectly distributed rows in the LOB subtables.
  • checks for missing primary or fallback row, where fallback exists for LOBs.
  • compares checksum of the primary and fallback copies of the LOB row.
  • verifies that hash codes reflect correct row distribution in each subtable.
  • checks for stale locators/OIDs where the update tag of the OID in the base row does not match the update tag in the large LOB row.
  • checks for gaps in the LOB, for example, if the first and third portions of the LOB are present, but the second piece is missing from the subtable.
USIs
  • detects non-indexed data rows, indexed data rows that no longer exist in the data subtable, and multiply indexed data rows.
  • checks for duplicate, out-of-order, and incorrectly distributed row IDs in primary and fallback index subtables.
  • verifies the field size of each field in the USI row with the defined maximum field size for the corresponding column. The field sizes are checked in the primary USI rows, not the fallback USI rows. Any field size error in the fallback USI rows is discovered in the byte-by-byte comparison between the primary and fallback USI rows.
  • for tables with fallback, makes a byte-by-byte comparison of primary and fallback index subtables.
  • compares the list of row IDs indexed in the primary or fallback index subtable with row IDs in primary or fallback data subtable.
  • compares the key of each USI row to the expected key extracted from the data row.
  • verifies row hashes
  • verifies that every logical row is indexed only once.
  • checks for duplicate, out-of-order, and incorrectly distributed row IDs in the primary and fallback data subtables, if such checks have not already been performed during a different phase of checking.
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
  • checks that each data row on index subtables is indexed with the correct index key.
  • checks that the hash code for each NUSI row corresponds to the key value of the row.
    This check is not valid for geospatial indexes.
  • checks for duplicate and out-of-order index rows.
  • checks for duplicate, out-of-order, and incorrectly distributed row IDs in the primary and fallback data subtables, if such checks have not already been performed during a different phase of checking.
  • verifies the field size for each field in the primary and fallback NUSI rows with the defined maximum field size for the corresponding column.
  • for geospatial indexes CheckTable performs additional checks of the integrity and correctness of the geospatial index R-tree structures.
reference index subtables
  • checks for duplicate index values.
  • checks for incorrect hash code.
  • checks reference index subtables against parent tables. If CheckTable detects an error, it places a read lock on the parent table specified in the reference index. If the AMP cannot lock the parent table, CheckTable reports an error.
  • verifies the field size for each field in the reference index row with the defined maximum field size for the corresponding column.
SJIs
  • checks for out of order, duplicate, misplaced row IDs, or closed rows in SJI.
  • compares the SJI contents with the corresponding columns in the base temporal table rows.
  • checks for value equivalent rows that have overlapping temporal values.
  • verifies the field size for each field in the SJI row with the defined maximum field size for the corresponding column.

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.