15.00 - Basing the Partitioning Expression on Two or More Numeric Columns - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Basing the Partitioning Expression on Two or More Numeric Columns

This form uses arithmetic operations, typically multiplication and addition, on two or more numeric columns with suitably small value ranges. Assume a table with a three-digit product code and a two-digit store number. The store numbers count consecutively from 0, and there are fewer than 65 stores. This table can be partitioned as follows:

     PRIMARY INDEX (store_number, product_code, sales_date)
     PARTITION BY store_number * 1000 + product_code;

If many queries specify both store_number and product_code, this might be a useful partitioning expression. One downside is that it fails if the number of products grows to the point that a four-digit number is required, or if the number of stores expands beyond 64.

Note that the Optimizer assumes 65,535 partitions even though some might be empty. The table is not any larger because of the empty partitions, though the Optimizer default assumption that there are 65,535 partitions based on the specification might sometimes mislead it into making suboptimal plan choices.

Some disadvantages of this form are that the partitioning cannot be altered unless the table is empty and row partition elimination for queries is usually limited to constant or USING value equality conditions on both of the partitioning columns.

An alternative is to use multilevel partitioning, as demonstrated by the following CREATE TABLE request:

     PRIMARY INDEX (store_number, product_code, sales_date)
     PARTITION BY (RANGE_N(store_number BETWEEN 0
                                        AND    64 
                                        EACH    1),
                                        BETWEEN 1 
                                        AND   999 
                                        EACH    1));