This topic provides the table definitions for the following examples:
Example: Non-Valid MODIFY PRIMARY INDEX Statements through “: Drop and Add Partition Ranges and Delete Rows Outside the Defined Ranges” and “: Using MODIFY to Repartition a Table and Saving Resulting Nonvalid Rows in a Save Table” through “: Revalidating the Partitioning for a Table” use the following tables.
The orders table has a primary index on o_orderkey and single-level partitioning. The table also has a USI defined on o_orderkey.
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);
The orders_cp table is multilevel column-partitioned with row partitioning on the second partitioning level that is defined using the same partitioning expression as the only partitioning level of orders. The table also has a USI defined on o_orderkey.
CREATE TABLE orders_cp ( 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)) NO PRIMARY INDEX PARTITION BY (COLUMN, RANGE_N(o_orderdate BETWEEN DATE '1992-01-01' AND DATE '1998-12-31' EACH INTERVAL '1' MONTH)) UNIQUE INDEX (o_orderkey);