Teradata Database Message 5955 - 5955 - Analytics Database - Teradata Vantage

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.