Revalidating Table/Join Index Partitioning | ALTER TABLE | Vantage - Revalidating the Partitioning for Tables and Join Indexes With a HASH Function Partitioning Expression - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Analytics Database
Teradata Vantage
Release Number
June 2022
English (United States)
Last Update
Product Category
Teradata Vantage™

During a cross platform migration process, the database regenerates the table headers for partitioned tables and join indexes after they are restored or copied to the new system.

If the new system either uses a different hashing algorithm or has 20-bit hash buckets rather than 16-bit hash buckets, a change in the value returned by a hash function can cause the partitioning expression to evaluate differently, and the regenerated table headers can cause incorrect query results to be returned from partitioned tables and join indexes when you specify a partitioning expression using the HASHBUCKET function. For information about the HASHBUCKET function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

You must revalidate the partitioning of any affected tables or join indexes using the following procedure.

  1. Identify the partitioned tables and join indexes affected by this problem by running the script prior to the migration. You should then run the hashbucket_ppi.rpt script afterward.

    The script performs a SELECT request that finds and reports any tables or join indexes that must be revalidated after the migration occurs.

    If you want to submit a standalone SELECT request to return the rows that must be revalidated, you can submit the following request, which duplicates the request the script submits.

         SELECT TRIM(DataBasenamei)||'.'||TRIM(TVMNamei), TableKind
         FROM   DBC.TableConstraints AS tc JOIN DBC.TVM
                                           ON tc.TVMID=TVM.TVMID
                                           JOIN DBC.Dbase
                                           ON tc.DBaseId=Dbase.DatabaseId
         WHERE  ConstraintType = 'Q'
         AND    UPPER(TableCheck) LIKE '%HASHBUCKET%'
         ORDER BY 1;
    TO revalidate this database object … GO to this step …
    partitioned table 2.
    partitioned join index 5.
  2. Revalidate the rows in each of the data tables identified by (but not the join index rows).

    To revalidate data tables, this revalidation requires the null partition handler specified by save_table to ensure that rows are stored in the correct partitions.

    You cannot specify a WITH DELETE or WITH INSERT [INTO] save_table null partition handler clause for a partitioned join index.
    The script uses the following procedure for partitioned tables:
    1. Create a save table using the DDL for the affected partitioned table, but do not specify any partitioning or secondary indexes for save_table.

      This step only applies to revalidating a partitioned table. You cannot use a null partition handler to revalidate a partitioned join index.

    2. Submit the following ALTER TABLE request to revalidate the partitioning for table_name.
      ALTER TABLE database_name.table_name


      ALTER TABLE database_name.table_name
      WITH INSERT INTO save_table;
      Specifies the name of the database or user that contains table_name.
      Specifies the name of the affected partitioned table.
      Specifies the name of the table created to save the null partitions that are created when you revalidate the partitioned table.
  3. The action taken for this step depends on whether save_table is populated with rows or not.
    IF save_table THEN …
    is not populated with rows the revalidation process for the data table is complete.
    is populated with rows you have a choice of how to handle each row in save_table.
    • Redefine the partitioning of the original table so you can reinsert the row.


    • Save the row in a separate table.
  4. After successfully handling each of the rows in save_table, drop the null partition handler save table.
  5. Revalidate partitioned join index rows by dropping and then recreating each affected partitioned join index.