17.10 - Level One 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 checks, a level-one check inspects the following:
  • Specified system data structures
  • Data subtables
  • Large object (LOB) subtables
  • Unique and nonunique secondary indexes (USIs and NUSIs), including geospatial indexes
  • System-defined join indexes (SJIs)

The following table summarizes the specific level-one 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
table dictionary or table header
  • checks the partitioning definition in the table header for a partitioned table for validity for this operating environment, and checked for consistency and correctness.
  • if the COMPRESSCHECK option is used, compares the compress multi-value and name of compression algorithm UDF (if specified) from the table header to the corresponding information stored in the CompressValueList column of the DBC.TVFields table.

    For more information on compression, see Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ - Data Types and Literals, B035-1143.

To correct the table header or the partitioning of rows, use the ALTER TABLE statement with the REVALIDATE PRIMARY INDEX option. For more information on ALTER TABLE, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

reference index
  • checks whether any AMP has any reference index flagged as invalid.
  • compares the primary row count with the fallback row count, if the index is hashed and the table was defined with fallback.
  • compares the row count in the index subtable with the row count in the data subtable.
data subtable
  • compares the physical row count in the primary data subtable with the physical row count in the fallback data subtables per AMP in each cluster.

    “Physical row” means the low-level row structure that is stored by the file system. A physical row may store a table row or the data from one or more column partitions.

This check is performed only for tables that have fallback.

This check requires access to all AMPs in a cluster. If any AMP in a cluster is down or unavailable, no AMPs in that cluster are checked.
LOB subtable
  • compares row counts in the LOB subtable.
  • verifies that the primary copy of the LOB subtable matches the row count in the fallback subtables.
  • verifies that the logical row count of the base data table matches that of each LOB subtable associated with the base table. Note that in cases where the associated LOB value is NULL, there will be no LOB row in the LOB subtable.

In the case of LOB subtables, there is not a one-to-one correspondence in the row counts of the base data table and the LOB subtable, since LOBs may span multiple physical rows in the LOB subtable.

For example if a base data table row points to a LOB that is 198,000 bytes in length, there will be four rows in the LOB subtable for this object (three rows containing 64,000 bytes and one row containing 6,000 bytes). When the counts are compared against the base data table, these four rows are counted by CheckTable as a single row.

USI checks if any AMP has any USIs flagged as invalid. CheckTable compares the following:
  • logical row counts between data subtables across all primary AMPs.
  • logical row counts in corresponding USI subtables across all primary AMPs.

    If the primary AMP is unavailable, and the table is fallback, then the appropriate fallback row counts are used instead. If the AMP is unavailable and the table is not fallback, this check is skipped.

  • compares logical row counts in the USI subtable, on each primary AMP, with the row count in the corresponding USI subtables of the other AMPs in the same cluster. However, this is done only if the table has fallback.

For USIs and NUSIs, an invalid index is not an error. Unless the index is excluded explicitly from the check, CheckTable issues a warning.

This check requires access to all AMPs in a cluster. If any AMP in a cluster is down or unavailable, no AMPs in that cluster are checked.
NUSI
  • checks if any AMP has any NUSIs flagged as invalid.
  • compares logical row counts of NUSI subtables to row counts for the corresponding data subtables, primary and fallback.
  • For geospatial indexes CheckTable performs additional checks of the integrity and correctness of the geospatial index R-tree structures.

Because the NUSI rows reside on the same AMP as the data rows, this check can be run without having all AMPs online. However, relationships between data and index subtables on unavailable AMPs are not checked.

For USIs and NUSIs, an invalid index is not an error. Unless the index is excluded explicitly from the check, CheckTable issues a warning.

SJI
  • compares the row counts of SJIs to row counts for the corresponding temporal primary data subtable (or fallback subtable, if an AMP in the cluster is down) to ensure counts are appropriate to the types of temporal table and temporal unique constraint.

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.