Level One Checks - Advanced SQL Engine - Teradata Database

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

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

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

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

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

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