Examples: PARTITION BY - Teradata Vantage

Example: Partitioned NUPI Specification Defined on a CURRENT_DATE Function

This example partitions an insurance customer table into historical (expired) policies and current policies using a CURRENT_DATE function in the partitioning expression.

CREATE TABLE customer (
  cust_name              CHARACTER(8),
  policy_number          INTEGER,
  policy_expiration_date DATE FORMAT 'YYYY/MM/DD')
PRIMARY INDEX (cust_name, policy_number)
PARTITION BY CASE_N(policy_expiration_date>= CURRENT_DATE, 
                                             NO CASE);

Example: Specifying a Partitioned NUPI with a USI Defined on the Same Column Set and Partitioned by a RANGE_N Function

This example creates a PPI on o_orderkey and a USI on the same column set. The request bases its partitioning expression on the RANGE_N function on o_orderdate.

The PPI cannot be defined as unique because its partitioning expression is based on o_orderdate and that column is not included in the primary index column set.

CREATE TABLE orders (
  o_orderkey      INTEGER NOT NULL,
  o_custkey       INTEGER,
  o_orderstatus   CHARACTER(1) CASESPECIFIC,
  o_totalprice    DECIMAL(13,2) NOT NULL,
  o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  o_orderpriority CHARACTER(21),
  o_clerk         CHARACTER(16),
  o_shippriority  INTEGER,
  o_comment       VARCHAR(79))
PRIMARY INDEX (o_orderkey)
PARTITION BY RANGE_N(o_orderdate BETWEEN DATE '1992-01-01' 
                                 AND     DATE '1998-12-31' 
                                 EACH INTERVAL '1' MONTH)
UNIQUE INDEX (o_orderkey);

Example: Partitioned Unique PI Specification over a Narrow Range Defined on a RANGE_N Function

This example creates a unique PI on store_id , product_id , and sales_date and bases its partitioning expression on the RANGE_N function on sales_date. No secondary indexes are defined.

The example provides partitions that are one day wide over a 5-month interval.

The PI can be defined as unique because the partitioning expression is based on sales_date and that column is included in the primary index column set.

CREATE TABLE sales (
  store_id      INTEGER NOT NULL,
  product_id    INTEGER NOT NULL,
  sales_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  total_revenue DECIMAL(13,2),
  total_sold    INTEGER,
  note          VARCHAR(256))
UNIQUE PRIMARY INDEX (store_id, product_id, sales_date)
PARTITION BY RANGE_N(sales_date BETWEEN DATE '2001-01-01' 
                                AND     DATE '2001-05-31' 
                                EACH INTERVAL '1' DAY);

Example: Partitioned NUPI Specification without a USI and Partitioned by a RANGE_N Function

This example creates a nonunique PI on l_orderkey and bases the partitioning expression on the RANGE_N function on l_shipdate. No secondary indexes are defined.

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(10),
  l_comment       VARCHAR(44))
PRIMARY INDEX (l_orderkey)
PARTITION BY RANGE_N(l_shipdate BETWEEN DATE '1992-01-01' 
                                AND     DATE '1998-12-31' 
                                EACH INTERVAL '1' MONTH);

Example: Partitioned UPI Specification Defined on an EXTRACT Function

This example creates a unique PI on store_id, product_id, and sales_date and bases the partitioning expression on a simple EXTRACT function on sales_date. No secondary indexes are defined on sales_by_month.

CREATE TABLE sales_by_month (
  store_id      INTEGER NOT NULL,
  product_id    INTEGER NOT NULL,
  sales_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  total_revenue DECIMAL(13,2),
  total_sold    INTEGER,
  note          VARCHAR(256))
UNIQUE PRIMARY INDEX (store_id, product_id, sales_date)
PARTITION BY EXTRACT(MONTH FROM sales_date);

Example: Partitioned UPI Specification over a Broad Range Defined on a RANGE_N Function

This example provides partitions that are one week wide over a 4-year interval:

CREATE TABLE sales_history (
 store_id      INTEGER NOT NULL,
 product_id    INTEGER NOT NULL,
 sales_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
 total_revenue DECIMAL(13,2),
 total_sold    INTEGER,
 note          VARCHAR(256))
UNIQUE PRIMARY INDEX (store_id, product_id, sales_date)
PARTITION BY RANGE_N(sales_date BETWEEN DATE '1997-01-01' 
                                AND     DATE '2000-12-31' 
                                EACH INTERVAL '7' DAY);

Example: PI Specification Defined with CASE_N Partitioning

The following example creates a NUPI on store_id, product_id, and sales_date and bases the CASE_N partitioning expression on total_revenue. Because total_revenue is not defined in the primary index column set, the primary index on this table cannot be defined as unique.

CREATE TABLE store_revenue (
  store_id      INTEGER NOT NULL,
  product_id    INTEGER NOT NULL,
  sales_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  total_revenue DECIMAL(13,2),
  total_sold    INTEGER,
  note          VARCHAR(256))
PRIMARY INDEX (store_id, product_id, sales_date)
PARTITION BY CASE_N(total_revenue <   10000, 
                    total_revenue <  100000,
                    total_revenue < 1000000, 
                    NO CASE, UNKNOWN);

If the column set (store_id , product_id , sales_date ) must be unique, define a USI on that set.

Example: Illegal Specification of UPI without All Partitioning Columns Included in Primary Index Definition

The following example attempts to create a unique PI on store_id, product_id, and sales_date, but fails because total_revenue on which the table is partitioned, is not included in the unique PI definition. A PI cannot be unique unless all its partitioning columns are included in the index definition.

CREATE TABLE store_revenue (
  store_id      INTEGER NOT NULL,
  product_id    INTEGER NOT NULL,
  sales_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  total_revenue DECIMAL(13,2)
  total_sold    INTEGER
  note          VARCHAR(256))
UNIQUE PRIMARY INDEX (store_id, product_id, sales_date)
PARTITION BY CASE_N(total_revenue <   10000, 
                    total_revenue <  100000, 
                    total_revenue < 1000000, 
                    NO CASE, UNKNOWN);

Example: Partitioned UPI Specification Using a Simple Arithmetic Expression

The following example creates a unique PPI on store_id , product_id , and sales_date and bases its partitioning expression on a simple arithmetic expression on store_id and product_id. No secondary indexes are defined.

CREATE TABLE store_product (
  store_id      INTEGER NOT NULL BETWEEN 0 AND 64,
  product_id    INTEGER NOT NULL BETWEEN 0 and 999,
  sales_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  total_revenue DECIMAL(13,2),
  total_sold    INTEGER,
  note          VARCHAR(256))
UNIQUE PRIMARY INDEX (store_id, product_id, sales_date)
PARTITION BY store_id*1000 + product_id + 1;

Example: Multilevel Partitioned NUPI with No USI Defined on the Primary Index

The following example creates a two-level partitioning using the RANGE_N function as the basis for both partitioning expressions. Because neither partitioning column is a component of the primary index, that index must be defined as a NUPI, and because no USI is defined on o_orderkey, that index is not unique.

     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(10),
       l_comment       VARCHAR(44))
     PRIMARY INDEX (l_orderkey)
     PARTITION BY (RANGE_N(l_suppkey  BETWEEN 0 
                                      AND  4999 
                                      EACH   10),
                   RANGE_N(l_shipdate BETWEEN DATE '2000-01-01' 
                                      AND     DATE '2006-12-31' 
                                      EACH INTERVAL '1' MONTH));

Example: Multilevel Partitioned UPI

The following example creates a two-level partitioning using the RANGE_N function as the basis for both partitioning expressions. Because both partitioning columns are components of the primary index, the index can be defined as a UPI.

     CREATE TABLE sales_history (
       store_id      INTEGER NOT NULL,
       product_id    INTEGER NOT NULL,
       sales_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       total_revenue DECIMAL(13,2),
       total_sold    INTEGER,
       note          VARCHAR(256))
     UNIQUE PRIMARY INDEX (store_id, product_id, sales_date)
     PARTITION BY (RANGE_N(store_id   BETWEEN 1 
                                      AND   300 
                                      EACH    1),
                   RANGE_N(sales_date BETWEEN DATE '2004-01-01' 
                                      AND     DATE '2006-12-31' 
                                      EACH INTERVAL '1' MONTH));

Example: Multilevel Partitioned NUPI with Maximum Number of Partitions

The following example specifies the maximum of 65,535 (3*5*17*257) partitions allowed for a 2-byte partition number combined partitioning expression. Because none of the partitioning columns is a component of the primary index, that index cannot be defined as a UPI.

     CREATE TABLE markets (
       product_id       INTEGER NOT NULL,
       region           BYTEINT NOT NULL,
       activity_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       revenue_code     BYTEINT NOT NULL,
       business_sector  BYTEINT NOT NULL,
       note             VARCHAR(256))
     PRIMARY INDEX (product_id, region) PARTITION BY (
       RANGE_N(region          BETWEEN 1 
                               AND     9 
                               EACH    3),
       RANGE_N(business_sector BETWEEN 0 
                               AND    49 
                               EACH   10),
       RANGE_N(revenue_code    BETWEEN 1 
                               AND    34 
                               EACH    2),
       RANGE_N(activity_date   BETWEEN DATE '1986-01-01' 
                               AND     DATE '2007-05-31' 
                               EACH INTERVAL '1' MONTH));

Example: Using Different Syntax to Create the Same Column-Partitioned Table Definition or to Change the Partitioning

This example presents three different syntaxes that all define the identical table.

The first example defines a column-partitioned table with a USI on column o_orderkey. Each column except for o_comment is contained in its own partition, is stored using system-determined COLUMN format, and is autocompressed. Column o_comment is in its own partition and is stored using ROW format without autocompression.

Because its maximum combined partition number (including 2 column partitions for internal use and 1 spare) is less than 65,535, the partition number of each row in this table uses a 2-byte partition number field in its row header.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       ROW(o_comment  VARCHAR(79)) NO AUTO COMPRESS)
     PARTITION BY COLUMN,
     UNIQUE INDEX (o_orderkey);

The difference for the second example is the absence of the ROW specification for o_comment in the column list, replaced by an ALL BUT (ROW(o_comment)) specification in the PARTITION BY COLUMN clause.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       o_comment      VARCHAR(79))
     PARTITION BY COLUMN ALL BUT (ROW(o_comment) NO AUTO COMPRESS),
     UNIQUE INDEX (o_orderkey);

The difference for the third example is that the columns that are not listed in the grouping clause of the COLUMN clause are grouped together into a column partition by default, but because o_shippriority is the only column that is not listed in the COLUMN grouping clause, each column is in its own single-column partition.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       o_comment      VARCHAR(79))
     PARTITION BY COLUMN (o_orderkey, o_custkey, o_orderstatus,
                          o_totalprice, o_orderdate, 
                          ROW o_comment NO AUTO COMPRESS),
     UNIQUE INDEX (o_orderkey);

This example again defines a column-partitioned orders table, but the partitioning is different from that used in the previous example. Each column is in its own partition with system-determined COLUMN format and autocompression except for the o_totalprice and o_comment columns, which are grouped together in a partition that has ROW format and autocompression.

Because the maximum number of combined partitions is 65,534 and the maximum column partition number is 65,535, the partition number for each row of this table uses 2-byte partitioning.

You can specify an ADD clause for the partitioning level of this table with a value as large as 65,526 and the syntax defines the same table.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       o_comment      VARCHAR(79))
     PARTITION BY COLUMN ALL BUT (ROW(o_totalprice, o_comment)),
     UNIQUE INDEX (o_orderkey);

This example again defines a column-partitioned orders table, but the partitioning is different from the previous examples. Each column is in its own partition. This request defines 7 partitions plus the 2 internal partitions used by all column-partitioned tables.

Because the maximum number of column partitions for this table is 9,223,372,036,854,775,806 and the maximum column partition number is 9,223,372,036,854,775,807, each row of this table requires 8-byte partitioning, which also distinguishes this example from the preceding examples of column-partitioned tables.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       o_comment      VARCHAR(79))
     NO PRIMARY INDEX PARTITION BY COLUMN ADD 68000,
     UNIQUE INDEX (o_orderkey);

This example defines the identical orders table as the previous example, the only difference being the value specified for the ADD option in the partitioning expression. Each column is in its own partition. This request defines 7 partitions plus the 2 internal partitions used by all column-partitioned tables.

Because the maximum number of column partitions for this table is 9,223,372,036,854,775,806 and the maximum column partition number is 9,223,372,036,854,775,807, the partition number for each row of this table requires 8-byte partitioning, which also distinguishes this example from the preceding examples of column-partitioned tables.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       o_comment      VARCHAR(79))
     PARTITION BY COLUMN ADD 9223372036854775797,
     UNIQUE INDEX (o_orderkey);

Example: Creating a Column-Partitioned Table with ROW Format

This example defines a column-partitioned table with 3 column partitions, all having ROW format either explicitly specified or system-determined.

     CREATE MULTISET TABLE sensordata (
       id VARCHAR (128),
       m1 VARBYTE(10000),
       m2 VARBYTE(10000),
       m3 VARBYTE(10000),
       t1 TIMESTAMP(6) WITH TIME ZONE,
       t2 TIMESTAMP(6) WITH TIME ZONE,
       t3 TIMESTAMP(6) WITH TIME ZONE,
       t4 TIMESTAMP(6) WITH TIME ZONE )
     PARTITION BY COLUMN (ROW(id), (m1, m2, m3),                          ROW(ts1, ts2, ts3, ts4));

Example: Using the ADD Option for a Column-Partitioned Table

This example defines a maximum of 9,999 column partitions (3 user-specified, 2 for internal use, and 9,994 for adding more partitions) and a maximum column partition number of 10,000.

The maximum combined partition number is the product of the maximum number of partitions at each level. For table t23, the maximum combined partition number is calculated as follows.

Maximum combined partition number = 10000 x 10000000 x 92233720 = 9223372000000000000

This is a valid maximum (does not exceed the limit of 9,223,372,036,854,775,807). Even though there are excess combined partitions, there are not enough to be able to add a partition to level 2 or to level 3, so the number of partitions at levels 2 and 3 cannot be increased using an ALTER TABLE request.

     CREATE MULTISET TABLE t23 (
       a INTEGER, 
       b INTEGER, 
       c INTEGER)
     PARTITION BY (COLUMN ADD 9994,
                   RANGE_N(a BETWEEN     1 
                             AND 10000000 
                             EACH 1),
                   RANGE_N(b BETWEEN    1 
                             AND 92233720 
                             EACH 1));

Suppose you change the RANGE_N end_expression value for level 3 to 92,233,721, which is only 1 larger than the previous end_expression value for level 3 of 92,233,720.

     CREATE MULTISET TABLE t23a (
       a INTEGER, 
       b INTEGER, 
       c INTEGER)
     PARTITION BY (COLUMN ADD 9994,
                   RANGE_N(a BETWEEN     1 
                             AND 10000000 
                             EACH 1),
                   RANGE_N(b BETWEEN    1 
                             AND 92233721 
                             EACH 1));

The table definition for t23a , like the definition for t23a, defines a maximum of 9,999 column partitions (3 user-specified, 2 for internal use, and 9,994 for adding more partitions), and also has a maximum column partition number of 10,000.

But notice what happens now when you calculate the maximum combined partition number for the table.

Maximum combined partition number = 10000 x 10000000 x 92233721 = 9223372100000000000

The maximum combined partition number now exceeds the system maximum value of 9,223,372,036,854,775,807, so Vantage returns an error to the requestor.

Example: Assigning Combined Partitions for a Column-Partitioned Table to Partitioning Levels

If there are remaining unassigned combined partitions after the other partitions have been assigned to their respective levels, Vantage attempts to assign those unassigned combined partitions to the previously defined partition levels, as this example shows.

The CREATE TABLE SQL text for this example follows.

CREATE TABLE t38 (
   a INTEGER, 
   b INTEGER, 
   c INTEGER)
PARTITION BY (COLUMN,             -- 3 specified partitions+2 int-
             RANGE_N(c BETWEEN 1     ernal=5 defined partitions.
                       AND    10),-- 1 defined partition. Note
             RANGE_N(b BETWEEN 1     there is no EACH clause.
                       AND  1000 
                       EACH    1) ADD 5);
                                  -- 1000 partitions.

• Partitioning Level 2

  Because partitioning level 2 is defined using a RANGE_N function without specifying an EACH clause, its number of defined partitions is 1. The initial maximum number of column partitions for level 2 is 1 because the definition does not specify an ADD constant. However, the maximum partitioning number for a partitioning level must be at least 2, so the actual maximum number of defined partitions for level 2 is 2, as is the maximum number of column partitions for the level.

  Preliminary number of defined partitions	1
  Default ADD constant value	1
  Final ADD constant value	3
  Default maximum number of defined column partitions	2
  Final maximum number of defined column partitions	4
  Preliminary maximum column partition number	4
  Final maximum column partition number	4

• Partitioning Level 3

  Level 3 has 1,000 defined partitions and a preliminary maximum partition number of 1005. This is derived by adding the ADD constant value, 5, to the initially defined 1,000 partitions.

  Preliminary number of defined partitions	1,000
  Default ADD constant value	5
  Final ADD constant value	23
  Default maximum number of defined column partitions	1,005
  Final maximum number of defined column partitions	1,023
  Preliminary maximum column partition number	1,005
  Final maximum column partition number	1,023

Because the product of the default maximum column partition numbers for each level, 16*2*1005 = 32,160, this table defines a 2-byte partitioning because 32,160 ≤ 65,335.

While Maximum Combined Partition Number ≤ 65,535, increasing the maximum partition number for any of the partitioning levels by only one causes the value of Maximum Combined Partition Number > 65,535, which is invalid.

The following SELECT request reports the values for table t38 in DBC.TableConstraints after Vantage has made its adjustments to the number of combined partitions assigned to each partitioning level of t38.

     SELECT * 
     FROM DBC.TableConstraints
     WHERE TVMId IN (SELECT TVMId 
                     FROM DBC.TVM
                     WHERE TVMNameI = 't38' 
                     AND   DataBaseId IN (SELECT DatabaseId 
                                          FROM DBC.DBASE 
                                          WHERE DatabaseNameI ='test')) 
     ORDER BY TVMId;

This request returns the following result set.