Example: 15 Levels of Multilevel Partitioning - Advanced SQL Engine - Teradata Database

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));