Level Three Checks - Advanced SQL Engine - Teradata Database

• 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.

• 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.

• 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.

• 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.

• 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.