15.00 - Revalidating the Partitioning for Tables and Join Indexes With a HASH Function Partitioning Expression - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Revalidating the Partitioning for Tables and Join Indexes With a HASH Function Partitioning Expression

During a cross platform migration process, Teradata 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 (see SQL Functions, Operators, Expressions, and Predicates for documentation of the HASHBUCKET function).

When this occurs, you must revalidate the partitioning of any affected tables or uncompressed join indexes using the following procedure.

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

Among other things, pre_upgrade_prep.pl 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 pre_upgrade_prep.pl 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 join index

5.

partitioned table

2.

2 Revalidate the rows in each of the data tables identified by pre_upgrade_prep.pl (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.

Note: 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.
  • a 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.

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

     
         ALTER TABLE database_name.table_name 
         REVALIDATE PRIMARY INDEX 
         WITH DELETE; 

    or

         ALTER TABLE database_name.table_name 
         REVALIDATE PRIMARY INDEX 
         WITH INSERT INTO save_table;

    where:

     

    Syntax element …

    Specifies the name of the …

    database_name

    database or user that contains table_name.

    table_name

    affected partitioned table.

    save_table

    table created to save the null partitions that are created when you revalidate the partitioned table.

  • The script uses the following procedure for column‑partitioned tables.
  • a Create a save table using the DDL for the affected partitioned table, but do not specify any partitioning or secondary indexes for save_table.

    b Submit the following ALTER TABLE request to revalidate the partitioning of a column‑partitioned table for table_name.

     
         ALTER TABLE database_name.table_name 
         REVALIDATE
         WITH DELETE; 

    or

         ALTER TABLE database_name.table_name 
         REVALIDATE
         WITH INSERT INTO save_table;

    where:

     

    Syntax element …

    Specifies the name of the …

    database_name

    database or user that contains table_name.

    table_name

    affected column‑partitioned table.

    save_table

    table created to contain null partitions after you revalidate the column‑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.
  • or

  • 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.