15.10 - PARTITION BY - Teradata Database

Specifies that a table is partitioned by one or more partitioning levels.

You can define a variety of partition types with a wide range in the number of combined partitions. However, you must consider the usefulness of defining a particular partitioning and its impact, both positive and negative, on performance and disk storage.

You cannot specify a partitioning level that includes a row-level security constraint column.

You cannot specify a character partitioning level for columns or constants that use the Kanji1 or KanjiSJIS server character sets.

A partitioned NoPI or PA table must be column partitioned and may also be row partitioned.

However, you can reference Period columns indirectly through the use of the BEGIN and END bound functions. See Example: CASE_N Partitioning Expression Using the END Bound Function.

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;