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 Row Partition Elimination.
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 );