15.00 - Detailed Multilevel Partitioning Example - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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