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 partitioning expressions other than RANGE_N or CASE_N, the Optimizer assumes a total of 65,535 partitions when statistics have not been collected, which can 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 x/10 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, specify an EACH clause.
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.
- 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 RANGE_N Function Syntax 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 not eliminated by row partition elimination, partitions with large numbers of rows can cause negative performance impacts.
- 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.