Level Two Checks - Advanced SQL Engine - Teradata Database

• performs checksum calculations for primary and corresponding fallback rows, and detects duplicate, out-of-order, and incorrectly distributed rows.
• for fallback tables, it checks primary against fallback.
• only row IDs of physical rows are checked for column-partitioned objects.

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.

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

• compares the list of row IDs indexed in the primary or fallback index subtable with logical row IDs in primary or fallback data subtable copy.
• for tables with fallback, compares row IDs in fallback copy to logical row IDs in primary.
• 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 the primary and fallback data subtables, if such checks have not already been performed during a different phase of checking.

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.

• checks for duplicate, out-of-order, and incorrectly distributed row IDs, if such checks have not already been performed during a different phase of checking.
• detects non-indexed data rows, indexed data rows that no longer exist in the data subtable, and multiply indexed data rows.
• checks any indexed data row IDs that belong to another AMP or belong in another subtable.
• compares lists of row IDs (indexed by index rows in the primary index subtable and each fallback index subtable) with the actual list of logical row IDs of data rows in the corresponding data subtables to ensure the list is in order.
• For geospatial indexes CheckTable performs additional checks of the integrity and correctness of the geospatial index R-tree structures.

• checks specified reference index subtables for duplicate, out-of-order row IDs, and incorrectly distributed rows.
• checks whether reference rows in the primary reference index subtable have corresponding rows in the fallback subtables.