Example: Revalidating the Partitioning for a Table - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Suppose that one of the following events causes you to suspect that incorrect partitioning of the orders table has occurred.

  • You suspect that partitioning might not be correct after a restore.
  • You copy the table to a system with different hardware or operating system.
  • A system malfunction has occurred.
  • The data dictionary has not been updated for tables created in an earlier release.

You can validate the partitioning of orders table rows using either of the following ALTER TABLE requests, with the first request deleting any problematic rows and the second moving them into a new table.

     ALTER TABLE orders 
     REVALIDATE WITH DELETE;
    
     ALTER TABLE orders 
     REVALIDATE WITH INSERT INTO old_orders;

Now assume that orders is column-partitioned and is named orders_cp. The following ALTER TABLE requests validate the partitioning of orders_cp table rows in the same way that they were validated in the previous part of this example.

     ALTER TABLE orders_cp 
     REVALIDATE WITH DELETE;
    
     ALTER TABLE orders_cp 
     REVALIDATE WITH INSERT INTO old_orders;