15.10 - Example: 15 Levels of Multilevel Partitioning - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
Release Number
December 2015
Content Type
Programming Reference
Publication ID
English (United States)

The following example demonstrates partitioning with the 2-byte partitioning maximum of 15 levels. A table with an 8-byte partitioning can have up to 62 partitioning levels.

In order not to exceed 65,535 partitions for the combined partitioning expression, each level must define a maximum of 2 partitions except for one level that can define a maximum of three partitions.

In this example, the last partitioning expression defines three partitions and the other partitioning expressions each define two partitions. Therefore, a total of 49,152 partitions are defined for the combined partitioning expression, which is fewer than the maximum of 65,535 for a 2-byte partition number.

Note that while the partitioning expressions are defined using a mix of RANGE_N and CASE_N function-based expressions, each level is defined atomically using either one or the other function, but not both. You cannot mix RANGE_N and CASE_N functions within any of the partitioning expressions, nor can a partitioning expression be based on anything but a RANGE_N or CASE_N function when you define a multilevel partitioning.

     CREATE TABLE product (
       product_id    INTEGER NOT NULL,
       p_color       INTEGER NOT NULL,
       p_size        INTEGER NOT NULL,
       p_width       SMALLINT NOT NULL,
       p_depth       SMALLINT NOT NULL,
       p_height      SMALLINT NOT NULL,
       p_weight      SMALLINT NOT NULL,
       p_catalog     INTEGER NOT NULL,
       p_code        BYTEINT NOT NULL,
       p_subcode     SMALLINT NOT NULL,
       rating        BYTEINT NOT NULL,
       discontinued  BYTEINT NOT NULL,
       in_stock      INTEGER NOT NULL,
       back_order    INTEGER NOT NULL,
       total_sold    INTEGER NOT NULL,
       product_date  DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       note          CHARACTER(255))
     PRIMARY INDEX (product_id)
     PARTITION BY (RANGE_N(p_color       BETWEEN 1 
                                         AND  1000 
                                         EACH  500),
                   RANGE_N(p_size        BETWEEN 1 
                                         AND 1000, 1001 
                                         AND 48000000),
                   RANGE_N(p_width       BETWEEN 1 
                                         AND   400 
                                         EACH  200),
                   RANGE_N(p_depth       BETWEEN 1 
                                         AND   400 
                                         EACH  200),
                   RANGE_N(p_height      BETWEEN 1 
                                         AND   400 
                                         EACH  200),
                   RANGE_N(p_weight      BETWEEN 1 
                                         AND  2000 
                                         EACH 1000),
                   CASE_N(p_catalog <= 1,NO CASE),
                   RANGE_N(p_code        BETWEEN 1 
                                         AND   100 
                                         EACH   50),
                   RANGE_N(p_subcode     BETWEEN 1 
                                         AND 10000 
                                         EACH 5000),
                   RANGE_N(rating        BETWEEN 1 
                                         AND    10 
                                         EACH    5),
                   CASE_N(discontinued = 0, 
                          discontinued = 1),
                   CASE_N(in_stock < 50,    NO CASE),
                   CASE_N(back_order = 0,   NO CASE),
                   CASE_N(total_sold < 1000,NO CASE),
                   RANGE_N(product_date BETWEEN DATE '2004-01-01' 
                                        AND     DATE '2006-12-31' 
                                        EACH INTERVAL '1' YEAR));