Example: Character Partitioning and System-Derived PARTITION[#Ln] Columns - Advanced SQL Engine - Teradata Database

Assume you have created the following tables:

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) NOT CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(21),
       o_comment       VARCHAR(79))
     PRIMARY INDEX (o_orderkey)
     PARTITION BY (RANGE_N(o_custkey BETWEEN 0
                                     AND 49999 
                                     EACH 1000),
                   RANGE_N(o_orderdate BETWEEN DATE '2000-01-01' 
                                       AND     DATE '2006-12-31'
                                       EACH INTERVAL '1' MONTH),
                   CASE_N(o_orderstatus = 'S', o_orderstatus = 'C',
                          o_orderstatus = 'F'))
     UNIQUE INDEX (o_orderkey);
     CREATE TABLE lineitem (
       l_orderkey      INTEGER NOT NULL,
       l_partkey       INTEGER NOT NULL,
       l_suppkey       INTEGER,
       l_linenumber    INTEGER,
       l_quantity      INTEGER NOT NULL,
       l_extendedprice DECIMAL(13,2) NOT NULL,
       l_discount      DECIMAL(13,2),
       l_tax           DECIMAL(13,2),
       l_returnflag    CHARACTER(1),
       l_linestatus    CHARACTER(1),
       l_shipdate      DATE FORMAT 'yyyy-mm-dd',
       l_commitdate    DATE FORMAT 'yyyy-mm-dd',
       l_receiptdate   DATE FORMAT 'yyyy-mm-dd',
       l_shipinstruct  VARCHAR(25),
       l_shipmode      VARCHAR(25),
       l_comment       VARCHAR(44))
     PRIMARY INDEX (l_orderkey)
     PARTITION BY (RANGE_N(l_suppkey BETWEEN 0 
                                     AND  4999 
                                     EACH  100),
                   RANGE_N(l_shipdate BETWEEN DATE '2000-01-01' 
                                     AND DATE '2006-12-31' 
                                     EACH INTERVAL '1' MONTH),
                   CASE_N(l_shipmode = 'USPS FirstClass', 
                          l_shipmode = 'UPS', 
                          l_shipmode = 'FedEx', 
                   NO CASE OR UNKNOWN));

The following are examples of the usage of the system-derived PARTITION columns.

Select rows for all customer orders with a status of S. The system does not return the value of PARTITION#L3 because it was not specified explicitly in the select list for the request. Note that it is not necessary to qualify PARTITION#L3 because the request references only one table.

     SELECT * 
     FROM orders 
     WHERE orders.PARTITION#L3=1;

Select rows for customers with IDs between 1000 and 1999 (partition 2 for level 1) with an order status of C (partition 2 for level 3). The system does not return the values of PARTITION#L1 and PARTITION#L3 because they were not specified explicitly in the select list for the request.

     SELECT * 
     FROM orders 
     WHERE orders.PARTITION#L1=2 
     AND   PARTITION#L3=2;

Select rows for suppliers with IDs between 3700 and 3799 (partition 38 for level 1) in January 2003 (partition 37 for level 2) where the shipping mode is FedEx (partition 3 for level 3). PARTITION is the partition number for the combined partitioning expression and is equal to (PARTITION#L1-1)*84*4+(PARTITION#L2-1)*4+(PARTITION#L3) where 84 is the number of partitions defined for level 2 and 4 is the number of partitions defined for level 3 for this example. That is, 12579 is derived from (38-1)*84*4+(37-1)*4+3.

Vantage does not return the value of PARTITION because it was not specified explicitly in the select list for the request.

     SELECT * 
     FROM lineitem 
     WHERE lineitem.PARTITION = 12579;

Select rows for ordered items with an order status of F that were shipped using UPS. Note that PARTITION#L3 must be qualified with lineitem because more than one table is referenced in the request.

     SELECT * 
     FROM orders, lineitem 
     WHERE orders.PARTITION#L3 = 3 
     AND   lineitem.PARTITION#L3 = 2 
     AND   orders.o_orderkey = lineitem.l_orderkey;