17.10 - PARTITION BY - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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.

You cannot define column partitioning for any of the following table types:
  • Global temporary
  • Volatile

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

A partitioning expression cannot specify external or SQL UDFs or columns having any of the following data types:
  • UDT
  • ARRAY/VARRAY
  • BLOB
  • CLOB
  • Period
  • XML
  • Geospatial
  • JSON
  • DATASET

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;