17.10 - Level Two 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)
In addition to performing level-pendingop and level-one checks, level-two checking does the following:
  • Determines whether row IDs on any given subtable are consistent with row IDs on other subtables by comparing lists of these IDs in those objects.
  • Computes and compares the checksum of primary and fallback rows.
  • Verifies that hash codes reflect correct row distribution in each subtable.

The following table summarizes the specific level-two 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
  • 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.

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

NUSI
  • 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.
reference index check
  • 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.
If the reference index subtable is fallback, Vantage uses the row IDs of the reference index rows from the primary copy of the subtables.
SJI
  • checks subtables for specified SJIs to detect non-indexed valid data rows, indexed valid data rows that no longer exist in the corresponding temporal table, and valid data rows that are indexed more than once.
  • checks subtables for specified SJIs for duplicate, out-of-order row IDs, closed rows, and incorrectly distributed rows.
  • compares the list of row IDs in the SJI with the actual row IDs of valid rows in the corresponding temporal table.

SJIs are generated by Vantage for temporal tables that have temporal unique constraints. For more information on temporal tables and constraints, see Teradata Vantage™ - Temporal Table Support, B035-1182.

If you run SCANDISK and correct any problems before using CheckTable, then it should not detect any duplicate or out-of-order rows during level-two checking.

Spool Space Requirements for Level Two Checks

Critical to level-two and level-three checking is the amount of spool space available on the system. The following formulas determine the amount of spool space required to perform these two levels of checking:
  • RID = 32,020 * (# of rows / 3,200)
  • SIS = (52 * # of rows) + (3,000,000 * # of AMPs)

To determine the number of rows, count the rows in the primary data table, which is the same as the primary index table. The following table shows the values and spool space required in the above formulas.

The value … Is the spool space required …
RID for the data subtable.
SIS by the largest secondary index subtable, whether it is unique or nonunique.

Therefore, to perform a level-two check for a nonfallback table that involves the secondary index, SIS requires a total spool space equal to the following:

RID + (SIS*2)

If the table contains fallbacks, then SIS requires a total spool space equal to the following:

2 *(RID + (SIS*2))

If you specify PARALLEL mode, then the total required spool space equals the sum of the spool space required for each of the tables being checked.