Assume you have created the following tables:
CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHARACTER(1) NOT CASESPECIFIC, o_totalprice DECIMAL(13,2) NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_orderpriority CHARACTER(21), o_comment VARCHAR(79)) PRIMARY INDEX (o_orderkey) PARTITION BY (RANGE_N(o_custkey BETWEEN 0 AND 49999 EACH 1000), RANGE_N(o_orderdate BETWEEN DATE '2000-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH), CASE_N(o_orderstatus = 'S', o_orderstatus = 'C', o_orderstatus = 'F')) 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(25), l_comment VARCHAR(44)) PRIMARY INDEX (l_orderkey) PARTITION BY (RANGE_N(l_suppkey BETWEEN 0 AND 4999 EACH 100), RANGE_N(l_shipdate BETWEEN DATE '2000-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH), CASE_N(l_shipmode = 'USPS FirstClass', l_shipmode = 'UPS', l_shipmode = 'FedEx', NO CASE OR UNKNOWN));
The following are examples of the usage of the system-derived PARTITION columns.
Select rows for all customer orders with a status of S. The system does not return the value of PARTITION#L3 because PARTITION#L3 was not specified explicitly in the select list for the request. You need not qualify PARTITION#L3 because the request references only one table.
SELECT * FROM orders WHERE orders.PARTITION#L3=1;
Select rows for customers with IDs between 1000 and 1999 (partition 2 for level 1) with an order status of C (partition 2 for level 3). The system does not return the values of PARTITION#L1 and PARTITION#L3 because these partitions were not specified explicitly in the select list for the request.
SELECT * FROM orders WHERE orders.PARTITION#L1=2 AND PARTITION#L3=2;
Select rows for suppliers with IDs between 3700 and 3799 (partition 38 for level 1) in January 2003 (partition 37 for level 2) where the shipping mode is FedEx (partition 3 for level 3). PARTITION is the partition number for the combined partitioning expression and is equal to (PARTITION#L1-1)*84*4+(PARTITION#L2-1)*4+(PARTITION#L3) where 84 is the number of partitions defined for level 2 and 4 is the number of partitions defined for level 3 for this example. That is, 12579 is derived from (38-1)*84*4+(37-1)*4+3.
Vantage does not return the value of PARTITION because that value was not specified explicitly in the select list for the request.
SELECT * FROM lineitem WHERE lineitem.PARTITION = 12579;
Select rows for ordered items with an order status of F that were shipped using UPS. PARTITION#L3 must be qualified with lineitem because more than one table is referenced in the request.
SELECT * FROM orders, lineitem WHERE orders.PARTITION#L3 = 3 AND lineitem.PARTITION#L3 = 2 AND orders.o_orderkey = lineitem.l_orderkey;