Considerations for Basing the Partitioning Expression on a RANGE_N Function
Using the RANGE_N function to build a partitioning expression offers the following
advantages:
Defining an efficient mapping or ranges between integer (BYTEINT, SMALLINT, INTEGER,
BIGINT), character (CHARACTER, GRAPHIC, VARCHAR, VARCHAR(n) CHARACTER SET GRAPHIC),
DATE, or TIMESTAMP type and INTEGER numbers.
Provides more opportunities than other expressions to optimize queries.
The Optimizer knows the number of defined partitions when you specify RANGE_N to define
the partitioning expression.
For other partitioning expressions, the Optimizer generally assumes a total of 65,535
partitions when statistics have not been collected, which could easily be far more
than the number of populated partitions. However, collecting statistics on PARTITION
can provide information about which partitions are empty.
Faster partitioning changes than any other expression using the following ALTER TABLE
options.
ADD RANGE
DROP RANGE
You can optimize the effects of using RANGE_N in your partitioning expression by observing
the following guidelines:
Reference only a single integer (BYTEINT, SMALLINT, INTEGER, BIGINT), character (CHARACTER,
GRAPHIC, VARCHAR, VARCHAR(n) CHARACTER SET GRAPHIC), DATE, or TIMESTAMP column, not
expressions.
For example, specifying a simple expression such as 
in place of a column name in the RANGE_N specification, even if the expression references
only a single column, can hinder, or even prevent, row partition elimination.
For equal‑sized ranges, always specify an EACH clause.
Note the following collateral facts about equal‑ and unequal‑sized partitions:
The performance of unequal‑sized partitions varies depending on which partitions are
accessed.
Unequal size ranges can prevent fast partitioning changes from being made using the
ALTER TABLE statement (see SQL Data Definition Language).
Using the NO RANGE, NO RANGE OR UNKNOWN, or UNKNOWN specifications for a range can
negatively affect later ALTER TABLE partitioning strategies.
Do not use these clauses unless you have specific reasons for doing so (see SQL Functions, Operators, Expressions, and Predicates for details).
Reasons not to use the NO RANGE, NO RANGE OR UNKNOWN, and UNKNOWN clauses include
the following.
The maintenance and use of partitioned tables is simpler if you do not use these options,
and avoiding their use also prevents bad data from being inserted into the table.
If these partitions are not eliminated by row partition elimination, they can cause
negative performance impacts if they contain a large of number of rows.
If a partitioning column is NOT NULL such that test values can never be null, do not
specify UNKNOWN or NO RANGE OR UNKNOWN.
If the specified ranges cover all possible values, do not specify NO RANGE or NO RANGE
OR UNKNOWN.