15.00 - ALTER TABLE (REVALIDATE Option) - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

ALTER TABLE (REVALIDATE Option)

General Rules and Restrictions for the REVALIDATE Option

The REVALIDATE option regenerates the table headers for a partitioned table or uncompressed join index and optionally verifies and corrects the row partitioning for a table (but not for a join index) if you specify the WITH DELETE or WITH INSERT null partition handling options.

You can specify the REVALIDATE option for unpartitioned tables and join indexes without also specifying PRIMARY INDEX. This makes revalidation applicable whether or not the table or join index has a primary index.

You must specify the REVALIDATE option without also specifying PRIMARY INDEX for NoPI tables and column‑partitioned tables and join indexes. Otherwise, Teradata Database aborts the request and returns an error to the requestor.

The REVALIDATE option also provides the ability to revalidate some data dictionary columns.

REVALIDATE requests do not verify the column partitioning of a column‑partitioned table or join index.

If partitioning errors are detected for a table (but not a join index), an ALTER TABLE REVALIDATE request corrects them.

REVALIDATE also revalidates the following Data Dictionary table columns.

 

                Table

            Column Name

             Validation Action Taken

DBC.TVM

 

PIColumnCount

Set to the number of primary index columns if the value is currently 0.

PartitioningLevels

Set to the number of partitioning levels if the value is currently 0.

DBC.TVFields

PartitioningColumn

Set to Y if the value is currently N.

DBC.TableConstraints

  • DefinedCombinedPartitions
  • MaxCombinedPartitions
  • PartitioningLevels
  • Set to the appropriate values if the value is currently 0.

    After an upgrade, the Data Dictionary columns in the preceding table might not be set correctly for existing tables and join indexes that were created before those columns were added to the Data Dictionary. In that case, you should submit an ALTER TABLE request for the following cases to deal with the problem.

     

    IF the table or join index …

    You should submit an ALTER TABLE request with this option to correct the Data Dictionary column values …

    has a primary index

    REVALIDATE PRIMARY INDEX

    does not have a primary index

    REVALIDATE

    You might need to revalidate the table headers for a partitioned table or join index when any of the following conditions exist.

  • The table headers for a partitioned table or uncompressed join index are marked as non‑valid.
  • This is most often found after an upgrade or after restoring or copying a table to a system that has a different operating system than the source or that runs on different hardware.

  • The RoundHalfwayMagUp parameter in the DBS Control record is changed and a partitioning expression specifies DECIMAL operations.
  • A change in DECIMAL rounding rules can cause the partitioning expression to evaluate differently.

    If this occurs for a uncompressed join index, drop the index and then recreate it (see “CREATE JOIN INDEX” on page 340).

  • The table or uncompressed join index is restored or copied to another system that has a different RoundHalfwayMagUp setting in its DBS Control record and the partitioning expression for the table or uncompressed join index specifies DECIMAL operations.
  • A change in DECIMAL rounding rules can cause the partitioning expression to evaluate differently.

  • The table or uncompressed join index is restored or copied to another system and the partitioning expression specifies floating point operations.
  • Floating point calculations might cause the partitioning expression to evaluate differently on different systems.

  • The table or uncompressed join index is restored or copied to another system that has a different hashing algorithm and the partitioning expression specifies a HASHROW or HASHBUCKET function.
  • A different hashing function can cause the partitioning expression to evaluate differently.

  • Teradata Database is upgraded in such a way that the calculation of the partitioning expressions for tables and uncompressed join indexes changes.
  • The CheckTable utility or valid SQL queries indicate rows with incorrect internal partition numbers or rows in the wrong partition for the table or uncompressed join index.
  • If this occurs unexpectedly, please report the problem to the GSC immediately.

    If this occurs for an uncompressed join index, drop the index and then recreate it (see “CREATE JOIN INDEX” on page 340).

    The following rules apply to the REVALIDATE option.

  • If you do not specify PRIMARY INDEX for a REVALIDATE request, the table or uncompressed join index can have either a partitioned primary index or no primary index. This specification includes column‑partitioned tables and join indexes.
  • You cannot specify REVALIDATE PRIMARY INDEX for a table or join index that does not have a primary index.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify REVALIDATE PRIMARY INDEX, the primary index for the table or uncompressed join index must be row‑partitioned.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • You cannot modify both primary index or partitioning definitions and revalidate a primary index or partitioning within a single ALTER TABLE request because the operations are mutually exclusive.
  • You cannot specify a WITH DELETE or WITH INSERT null partition handling clause for a table that does not have row partitioning.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • You cannot specify a WITH DELETE or WITH INSERT null partition handling clause for a join index because the null partition handling options are only valid for tables.
  • If you attempt to revalidate a join index using a null partition handling clause, Teradata Database aborts the request and returns an error to the requestor.

    If you suspect or detect a problem with the rows of a uncompressed join index, you must drop and then recreate it.

  • If you specify neither a WITH DELETE nor a WITH INSERT null partition handling clause for the option, then only table headers are regenerated and no error checking or repair is performed.
  • If you specify either a WITH DELETE or a WITH INSERT null partition handling clause, then in addition to regenerating the table headers for the table, all the rows in a base table are validated for their partition number and row hash value.
  • If you specify a WITH DELETE null partition handling clause, then Teradata Database deletes any row for which a partitioning expression does not generate a value between 1 and 65,535 (after casting to INTEGER if the value is not already typed as INTEGER or CHARACTER).
  • If you specify a WITH INSERT [INTO] save_table null partition handling clause, Teradata Database deletes any row for which a partitioning expression does not generate a value between 1 and 65,535 (after casting to INTEGER if the value is not already typed as INTEGER or CHARACTER from the table and inserts it into save_table.
  • The following rules apply to the use of a save_table with a null partition handling clause.

  • save_table cannot be the same table as table_name or the system returns an error.
  • save_table must have the same number of columns with compatible data types as table_name, otherwise the system returns an error.
  • If errors occur inserting into save_table, then the following rollback behavior occurs, depending on the session mode in effect.
  •  

    IF the session is in this mode …

    THEN the following block of work is rolled back …

    ANSI

    request.

    Teradata

    transaction.

  • If evaluation of a partitioning expression for a row causes evaluation errors, then Teradata Database rolls back any changes made to the table or to save_table and the partitioning is not changed.
  • Specific rollback behavior depends on the session mode in effect.

     

    IF the session is in this mode …

    THEN the following block of work is rolled back …

    ANSI

    request.

    Teradata

    transaction.

    If this occurs, you should correct the problem using one of the following solutions.

  • Make the primary index for a primary‑indexed table or uncompressed join index an unpartitioned primary index.
  • Change the partitioning expression to one that does not generate evaluation errors.
  • Delete rows that cause evaluation errors.
  • Drop the table.
  • For the remaining rows in the table, Teradata Database updates the internal partition numbers and row hash values as needed and, if the row is not in the correct partition or row hash, it is moved to the correct location.
  • Rows that are out of order by their assigned ROWID are not corrected and Teradata Database returns an error to the requestor.

  • Teradata Database updates any secondary indexes, join indexes, or hash indexes defined on the table as needed.
  • The values of the system-derived PARTITION or PARTITION#L[n] columns might change for the rows in the table.
  • When you revalidate a table, its version number is incremented because its structure is altered. When the version number changes, you cannot perform any of the following actions over a DDL request that alters table structure.

  • Cluster restore
  • Single AMP restore
  • Rollforward or rollback of a permanent journal
  • If the ARC utility encounters such a structural table change during a rollforward or rollback operation, then Teradata Database stops that operation and returns an error in the output listing.

  • If a partitioning expression for a table is affected by a time zone string (see “Daylight Saving Time and Time Zone Strings Specified As Time Zone Strings” on page 889) and the time zone rules for that string are changed, you must revalidate the table using the WITH INSERT or WITH DELETE null partition handling options.
  • The following rules apply to the REVALIDATE [PRIMARY INDEX] option for character partitioning.

  • All rules that apply to non‑character partitioning for this option also apply to column partitioning.
  • The other rules listed here apply only to column partitioning.

  • Character‑based partitioning might need to be revalidated if the following are all true.
  • The table or uncompressed join index was created with the CHARSET_COLL session collation, in which case Teradata Database uses the code point ordering of the session character set in effect when the table was created for the table or join index collation.
  • The collation character set is not one of the following predefined character sets.
  • ASCII
  • EBCDIC
  • UTF‑8
  • UTF‑16
  • The character set in the DBC.Translation table has been modified since the table was created.
  • Character‑based partitioning might need to be revalidated if the following are all true.
  • The table or uncompressed join index was created with MULTINATIONAL collation.
  • A different MULTINATIONAL collation has been installed in DBC.Collations since the table or uncompressed join index was created.
  • Character‑based partitioning might need to be revalidated if the character partitioning expression involves one or more Unicode character expressions or literals and Teradata Database was backed down to a previous minor release that has a different Unicode character set definition.
  • In other words, the table or uncompressed join index might need to be revalidated if the later release has code points that are undefined at the earlier release.

  • If the character‑based partitioning you need to revalidate is defined on an uncompressed join index, then you must drop and then recreate the index.
  • When Teradata Database rebuilds the character‑based partitioning and then moves rows to their correct partitions (assuming that you have specified a null partition handler), it uses the collation associated with the original table or uncompressed join index collation in the regenerated partitioning expression, not the session collation in effect when the ALTER TABLE … REVALIDATE PRIMARY INDEX request is submitted.
  • Teradata Database uses the default case sensitivity for the session mode that was in effect when the table or uncompressed join index was created or last had its partitioning altered in the regenerated partitioning expression.
  • The collation character set in effect when the table or uncompressed join index was created must exist in DBC.CharTranslations, must be installed, and must have the same numeric code (in the CharSetID column) as it did when the table or uncompressed join index was created or be one of the following predefined character sets.
  • ASCII
  • EBCDIC
  • UTF‑8
  • UTF‑16
  • If the character set is no longer installed, Teradata Database aborts the request and returns an error to the requestor.

    If you cannot reinstall the character set that was in effect when the table or uncompressed join index was created, then you cannot revalidate that table or join index. Instead, you must submit an appropriate INSERT … SELECT request to move the rows into a new table in which you can either generate valid partitioning or remove the current partitioning as is appropriate.

  • The following table indicates the differences in how the new collation for the character‑based partitioning is handled.
  •  

    IF the partitioning collation is …

    THEN Teradata Database uses …

    MULTINATIONAL

    the currently installed MULTINATIONAL collation as the new partitioning collation even if it differs from the MULTINATIONAL collation that was installed when the table or join index was created.

    CHARSET_COLL

    the collation character set used in the original partitioning collation for the new partitioning collation.