PARTITION BY - Teradata Vantage - Analytics Database

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 you need to ensure the uniqueness of the column set (store_id , product_id , sales_date ), you can 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;