15.00 - Level Three Checks - Teradata Database

Teradata Database Utilities

Teradata Database
Release Number
Content Type
Publication ID
English (United States)
Last Update

Level Three Checks

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 unavailable AMP in place of the primary copies on the unavailable AMP.

The following table summarizes level‑three checking.


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 the row hashes.
  • 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 Database Design.

    To correct the partitioning of rows for a table, use the ALTER TABLE statement with the REVALIDATE PRIMARY INDEX WITH DELETE or INSERT option. Fro more information on ALTER TABLE, see SQL Data Definition Language.

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