The following example demonstrates partitioning with the 2-byte partitioning maximum of 15 levels. A table with an 8-byte partitioning can have up to 62 partitioning levels.
In order not to exceed 65,535 partitions for the combined partitioning expression, each level must define a maximum of 2 partitions except for one level that can define a maximum of three partitions.
In this example, the last partitioning expression defines three partitions and the other partitioning expressions each define two partitions. Therefore, a total of 49,152 partitions are defined for the combined partitioning expression, which is fewer than the maximum of 65,535 for a 2-byte partition number.
Note that while the partitioning expressions are defined using a mix of RANGE_N and CASE_N function-based expressions, each level is defined atomically using either one or the other function, but not both. You cannot mix RANGE_N and CASE_N functions within any of the partitioning expressions, nor can a partitioning expression be based on anything but a RANGE_N or CASE_N function when you define a multilevel partitioning.
CREATE TABLE product ( product_id INTEGER NOT NULL, p_color INTEGER NOT NULL, p_size INTEGER NOT NULL, p_width SMALLINT NOT NULL, p_depth SMALLINT NOT NULL, p_height SMALLINT NOT NULL, p_weight SMALLINT NOT NULL, p_catalog INTEGER NOT NULL, p_code BYTEINT NOT NULL, p_subcode SMALLINT NOT NULL, rating BYTEINT NOT NULL, discontinued BYTEINT NOT NULL, in_stock INTEGER NOT NULL, back_order INTEGER NOT NULL, total_sold INTEGER NOT NULL, product_date DATE FORMAT 'yyyy-mm-dd' NOT NULL, note CHARACTER(255)) PRIMARY INDEX (product_id) PARTITION BY (RANGE_N(p_color BETWEEN 1 AND 1000 EACH 500), RANGE_N(p_size BETWEEN 1 AND 1000, 1001 AND 48000000), RANGE_N(p_width BETWEEN 1 AND 400 EACH 200), RANGE_N(p_depth BETWEEN 1 AND 400 EACH 200), RANGE_N(p_height BETWEEN 1 AND 400 EACH 200), RANGE_N(p_weight BETWEEN 1 AND 2000 EACH 1000), CASE_N(p_catalog <= 1,NO CASE), RANGE_N(p_code BETWEEN 1 AND 100 EACH 50), RANGE_N(p_subcode BETWEEN 1 AND 10000 EACH 5000), RANGE_N(rating BETWEEN 1 AND 10 EACH 5), CASE_N(discontinued = 0, discontinued = 1), CASE_N(in_stock < 50, NO CASE), CASE_N(back_order = 0, NO CASE), CASE_N(total_sold < 1000,NO CASE), RANGE_N(product_date BETWEEN DATE '2004-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' YEAR));