15.00 - Usage Examples for PARTITION - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Usage Examples for PARTITION

The following examples demonstrate usage of the system‑derived PARTITION column for single-level partitioned primary index tables only. They do not cover usage considerations for the combined PARTITION expression of multilevel partitioned primary index tables.

For an example of PARTITION usage in that context, see “Usage Examples for PARTITION#Ln” on page 812 and “Example 1” on page 813 through “Example 14” on page 818.

The following table definitions are used for this set of examples.

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

Example : Delete All Orders From a Specific Partition

Delete all orders in the orders table from partition 1.

     DELETE 
     FROM orders 
     WHERE orders.PARTITION = 1;

Example : Delete All Orders From a Range of Partitions

Delete all orders in the orders table from partitions 30 through 32, inclusive.

     DELETE 
     FROM orders 
     WHERE orders.PARTITION BETWEEN 30 AND 32;

Example : INSERT … SELECT Rows Into a Target Table From Several Partitions, Then Delete Them From the Source Table

Use an INSERT … SELECT statement to copy orders from partitions 1 and 2 of the orders table into the old_orders table, then delete them from orders.

     INSERT INTO old_orders 
     SELECT * 
     FROM orders 
     WHERE orders.PARTITION IN (1,2)
    ;DELETE FROM orders 
     WHERE orders.PARTITION IN (1, 2);

Example : PARTITION Values Not Returned Because PARTITION Not Specified in Select List

In the following example, the value of PARTITION is not returned as one of the column values, even though it is specified in the WHERE clause, because it was not explicitly specified in the select list for the query:

     SELECT * 
     FROM orders 
     WHERE orders.PARTITION = 10 
     AND   orders.o_totalprice > 19.99;

Example : Qualification of PARTITION Not Necessary Because Specification Is Unambiguous

PARTITION does not have to be qualified in this example because its use is unambiguous:

     SELECT orders.*, PARTITION 
     FROM orders 
     WHERE orders.PARTITION = 10 
     AND   orders.o_totalprice > 100; 

Example : Qualification of PARTITION Necessary Because of Ambiguity Otherwise

PARTITION must be qualified in the two following examples to distinguish between PARTITION values in the orders table and PARTITION values in the lineitem table:

     SELECT * 
     FROM orders, lineitem 
     WHERE orders.PARTITION = 3 
     AND   lineitem.PARTITION = 5 
     AND   orders.o_orderkey = Lineitem.l_orderkey;
 
     SELECT orders.*, lineitem.*, orders.PARTITION 
     FROM orders, lineitem
     WHERE orders.PARTITION = 3 
     AND   lineitem.PARTITION = 5 
     AND   orders.o_orderkey = lineitem.l_orderkey;

Example : Non-Valid Use of PARTITION In VALUES Clause of INSERT Statement

This example is not valid because PARTITION cannot be referenced in the VALUES clause:

     INSERT INTO Orders VALUES (PARTITION, 10, 'A', 599.99, 
       DATE '2001-02-07','HIGH', 'Jack', 3,'Invalid insert');

Example : Selection of All Active Partitions From a Table

The following two examples provide a list of the populated partitions in the orders table:

     SELECT DISTINCT PARTITION (FORMAT '999') 
     FROM Orders 
     ORDER BY PARTITION;
 
     SELECT DISTINCT CAST (PARTITION AS BYTEINT) 
     FROM Orders 
     ORDER BY PARTITION;

Example : Using PARTITION With An Aggregate Function

The following example counts the number of rows in each populated partition:

     SELECT PARTITION, COUNT(*) 
     FROM Orders 
     GROUP BY PARTITION 
     ORDER BY PARTITION;

Example : Using PARTITION In View Definitions

You cannot select PARTITION through this view because it was not specified explicitly in its select list:

     CREATE VIEW ordersv AS 
     SELECT * FROM orders;

Because it is explicitly selected in the following view definition, PARTITION values are returned to the requestor if all columns are selected from the view using the * literal:

     CREATE VIEW ordersvp AS 
     SELECT orders.*, PARTITION 
     FROM orders;

Example : A More Sophisticated Use of Partitioning

The following query finds orders for which at least one lineitem was shipped in the same month as the order was recorded:

     SELECT o.o_orderkey 
     FROM orders AS o, lineitem AS l
     WHERE o.PARTITION = l.PARTITION 
     AND   o.o_orderkey = l.l_orderkey
     GROUP BY o.o_orderkey;

Note that the primary indexes for both tables are partitioned in such a way that their rows are partitioned on the same date ranges, so that can be exploited as a WHERE clause search condition.