Detailed Multilevel Partitioning Example - Teradata Database

Detailed Multilevel Partitioning Example

Multilevel Partitioned Sales Table With 3 Levels of Row Partitioning

The following CREATE TABLE request defines a table with 3 levels of row partitioning.

     CREATE TABLE sales (

       storeid      INTEGER NOT NULL,

       productid    INTEGER NOT NULL,

       salesdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,

       totalrevenue DECIMAL(13,2),

       totalsold    INTEGER,

       note         VARCHAR(256))

     UNIQUE PRIMARY INDEX (storeid, productid, salesdate)

     PARTITION BY (RANGE_N(salesdate BETWEEN DATE '2003-01-01'

                                     AND     DATE '2005-12-31'

                                     EACH INTERVAL '1' YEAR),

                   RANGE_N(storeid   BETWEEN 1 

                                     AND   300 

                                     EACH  100),

                   RANGE_N(productid BETWEEN 1 

                                     AND   400 

                                     EACH  100));