The REVALIDATE option regenerates the table headers for a partitioned table or noncompressed 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 nonpartitioned 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 and primary AMP index tables and join indexes. Otherwise, the database 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), you can use an ALTER TABLE ... REVALIDATE statement including the WITH clause to correct 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 |
|
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. Regardless of whether the table or join index has a primary index, you should submit an ALTER TABLE statement with the REVALIDATE option to correct the Data Dictionary column values
- The table headers for a partitioned table or join index are marked as nonvalid.
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 partitioned join index, drop the index and then recreate it. See CREATE JOIN INDEX.
- The table or 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 noncompressed join index specifies DECIMAL operations.
A change in DECIMAL rounding rules can cause the partitioning expression to evaluate differently.
- The table or 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 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.
- The database is upgraded in such a way that the calculation of the partitioning expressions for tables and 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 join index.
If this occurs unexpectedly, please report the problem to the Teradata Support Center immediately.
If this occurs for a join index, drop the index and then recreate it. See CREATE JOIN INDEX.
- You cannot specify REVALIDATE PRIMARY INDEX for a table or join index that does not have a primary index.
Otherwise, the database returns an error to the requestor.
- You cannot modify 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, the database 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, the database returns an error to the requestor.
If you suspect or detect a problem with the rows of a noncompressed 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 the 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, the 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 the 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 noncompressed join index a nonpartitioned 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, the 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 the database returns an error to the requestor.
- The 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.
- 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) 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.
- Character-based partitioning might need to be revalidated if the following are all true.
- The table or join index was created with the CHARSET_COLL session collation, in which case the 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 both true.
- The table or noncompressed join index was created with MULTINATIONAL collation.
- A different MULTINATIONAL collation has been installed in DBC.Collations since the table or noncompressed 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 the database was backed down to a previous minor release that has a different Unicode character set definition.
The table or 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 a join index, then you must drop and then recreate the index.
- When the system rebuilds the character-based partitioning and 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 join index collation in the regenerated partitioning expression, not the session collation in effect when the ALTER TABLE … REVALIDATE request is submitted.
- The database uses the default case sensitivity for the session mode that was in effect when the table or 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 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 noncompressed 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, the database returns an error to the requestor.
If you cannot reinstall the character set that was in effect when the table or 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 the 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.