Here is an example that modifies “Example: Defining the Partition to Which a Row is Assigned” to use CASE_N in a list of partitioning expressions that define a multilevel PPI:
CREATE TABLE orders (storeid INTEGER NOT NULL ,productid INTEGER NOT NULL ,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL ,totalorders INTEGER NOT NULL) PRIMARY INDEX (storeid, productid) PARTITION BY (CASE_N(totalorders < 100, totalorders < 1000, NO CASE) ,CASE_N(orderdate <= '2005-12-31', NO CASE) );
The example defines six partitions to which a row can be assigned. The first CASE_N expression defines three partitions based on the value of the totalorders column. The second CASE_N expression subdivides each of the three partitions into two partitions based on the value of the orderdate column.
Level 1 Partition Number | Level 2 Partition Number | Condition |
---|---|---|
1 | 1 | The value of the totalorders column is less than 100 and the value of the orderdate column is less than or equal to '2005-12-31'. |
2 | The value of the totalorders column is less than 100 and the value of the orderdate column is greater than '2005-12-31'. | |
2 | 1 | The value of the totalorders column is less than 1000 but greater than or equal to 100, and the value of the orderdate column is less than or equal to '2005-12-31'. |
2 | The value of the totalorders column is less than 1000 but greater than or equal to 100, and the value of the orderdate column is greater than '2005-12-31'. | |
3 | 1 | The value of the totalorders column is greater than or equal to 1000 and the value of the orderdate column is less than or equal to '2005-12-31'. |
2 | The value of the totalorders column is greater than or equal to 1000 and the value of the orderdate column is greater than '2005-12-31'. |