The following example specifies character partitioning with a partitioning expression based on the CASE_N function. This example does not support static row partition elimination because the CASE_N function includes LIKE operators. See SQL Request and Transaction Processing, B035-1142.
CREATE TABLE accounts ( cust_id INTEGER, last_name VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC, first_name VARCHAR(30), city VARCHAR(50)) PRIMARY INDEX (cust_id) PARTITION BY CASE_N (last_name LIKE ‘A%’, last_name LIKE ‘B%’, last_name LIKE ‘C%’, last_name LIKE ‘D%’, last_name LIKE ‘E%’, last_name LIKE ‘F%’, last_name LIKE ‘G%’, last_name LIKE ‘H%’, last_name LIKE ‘I%’, last_name LIKE ‘J%’, last_name LIKE ‘K%’, last_name LIKE ‘L%’, last_name LIKE ‘M%’, last_name LIKE ‘N%’, last_name LIKE ‘O%’, last_name LIKE ‘P%’, last_name LIKE ‘Q%’, last_name LIKE ‘R%’, last_name LIKE ‘S%’, last_name LIKE ‘T%’, last_name LIKE ‘U%’, last_name LIKE ‘V%’, last_name LIKE ‘W%’, last_name LIKE ‘X%’, last_name LIKE ‘Y%’, last_name LIKE ‘Z%’, NO CASE, UNKNOWN);
The following example supports static row partition elimination because the CASE_N function does not include LIKE operators, which enhances the performance of queries on the table.
CREATE TABLE accounts ( cust_id INTEGER, last_name VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC, first_name VARCHAR(30), city VARCHAR(50)) PRIMARY INDEX (cust_id) PARTITION BY CASE_N (last_name < 'A', last_name < 'B', last_name < 'C', last_name < 'D', last_name < 'E', last_name < 'F', last_name < 'G', last_name < 'H', last_name < 'I', last_name < 'J', last_name < 'K', last_name < 'L', last_name < 'M', last_name < 'N', last_name < 'O', last_name < 'P', last_name < 'Q', last_name < 'R', last_name < 'S', last_name < 'T', last_name < 'U', last_name < 'V', last_name < 'W', last_name < 'X', last_name < 'Y', last_name >= 'Z', UNKNOWN);