Teradata Database Message 5955 - 17.10 - 5955 - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Messages

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1096-171K
Language
English (United States)
Message
Warning: Table headers have been revalidated but correct partitioning of the rows needs to be verified.
Explanation
While revalidating a table header for the specified table during an ALTER TABLE that defines new partitioning expression using DROP/ADD RANGE or during an ALTER TABLE with REVALIDATE PRIMARY option but no WITH option, the table headers for the table were corrected but the rows may be inconsistently partitioned per the updated table headers. If so, queries that are executed with partition elimination may get wrong results. This warning indicates the table header for the table was created in an earlier release with a partitioned primary index where one or more of the partitioning expressions were nondeterministic (contained time or timestamp constants without time zone, or AT LOCAL clause) or that some internal error occurred to cause an inconsistency in the table headers.
Generated By
Opt.
For Whom
End User and System Support Representative.
Notes
This is a warning but it is important to follow the instructions in the following remedy to make sure the rows are correctly partitioned.
Remedy
It is recommended that you contact your System Support Representative to assist in applying this remedy. Run the following query to verify if rows are correctly partitioned: SELECT COUNT(*) AS "TOTAL ROWS", SUM( CASE WHEN PARTITION#L1<>partitioning_expression_1 OR PARTITION#L2<>partitioning_expression_2 OR ... OR PARTITION#Ln<>partitioning_expression_n THEN 1 ELSE 0 END) AS "ROWS INCORRECTLY PARTITIONED" FROM table_name; If the number of rows incorrectly partitioned is zero, no further action is necessary. If the number of rows incorrectly partitioned is significantly less than the total number of rows (less than 1%) or the table is small, submit an ALTER TABLE table_name REVALIDATE PRIMARY INDEX WITH DELETE/INSERT statement to correctly partition the rows (use the WITH INSERT option if it is desired to capture any rows that may be deleted if they do not belong to any partition). If there are too many rows incorrectly partitioned, recreating the table and re-populating will probably be more efficient. The rows for the original table can be insert-selected from the original table into the new table or if there is not enough space on the system, the new table may be re-populated from a backup.