The RANGE_N function simplifies the specification of common partitioning expressions where each partition contains a range of numeric data, useful when the column contains a date or timestamp. RANGE_N returns an INTEGER determined by the first range that includes the column value, numbered from 1, that can be mapped directly to a partition number or further modified to calculate the partition number. RANGE_N is commonly used to define partitioning expressions. When you use a RANGE_N function to define a single-level partitioning expression, two partition numbers, NO RANGE and UNKNOWN, are reserved for specific uses.
Assume a table with 7 years of order data, ranging from 2001 through 2007. The next partitioning expression creates 84 partitions, one for each month of the period covered by the data:
CREATE TABLE … PRIMARY INDEX (order_number) PARTITION BY RANGE_N (order_date BETWEEN DATE '2001-01-01' AND DATE '2007-12-31' EACH INTERVAL '1' MONTH);
Each partition contains roughly the same number of rows, assuming that order volume has stayed roughly constant across the seven year interval. Neither a NO RANGE nor an UNKNOWN partition is defined because this partitioning expression definition is for data that only has dates within the ranges specified in the partitioning expression.
While typically desirable to have each partition contain approximately the same number of rows, but this is optional. The next example puts the older orders into partitions with coarser granularity, and the newer orders into partitions with finer granularity:
CREATE TABLE … PRIMARY INDEX (order_number) PARTITION BY RANGE_N (order_date BETWEEN DATE '1994-01-01' AND DATE '1997-12-31' EACH INTERVAL '2' YEAR, DATE '1998-01-01' AND DATE '2000-12-31' EACH INTERVAL '1' YEAR, DATE '2001-01-01' AND DATE '2003-12-31' EACH INTERVAL '6' MONTH, DATE '2004-01-01' AND DATE '2007-12-31' EACH INTERVAL '1' MONTH, NO RANGE, UNKNOWN);
In this example, more recent data is partitioned more finely than older data. If you know that the older data is rarely accessed except in a full-table scan, this strategy reduces potential disadvantages by defining a smaller number of partitions. However, maintaining this structure over an extended time is harder than maintaining a structure in which each interval covers the same time duration.
In this example, the years 2004 through 2007 are partitioned by month. As time passes, and 2004 data becomes older and less frequently referenced, you must repartition the table if the pattern of defining longer time intervals for older data is to be maintained.
Adding and dropping partitions from the ends of a partitioning definition is easy and fast. Repartitioning intervals in the middle partitions requires so much more work that reloading the data is typically faster. Also, when a range partition is dropped, any rows in that partition are moved from the dropped range partition to the NO RANGE partition or to an added range partition. Also, when a range is added, rows may need to be moved from the NO RANGE partition to the new range partition.
Expansion room is allowed for future dates by specifying the final partition as extending to the end of 2007. Adding ranges to, and dropping ranges from, the end of a partitioning expression using the ALTER TABLE statement is easy. Defining hundreds of partitions for future decades is undesirable.
The example shows intervals of years and months. You can also partition by day (EACH INTERVAL '1' DAY) or by week (EACH INTERVAL '7' DAY). A seven-day interval can start on any day of the week. To start the weekly intervals on Sunday, for example, choose a Sunday as the beginning date. The last range may be less than specified by the EACH clause. For example, in a seven-day interval, if the first and last dates fall on Sunday, the last range spans only one day.
The RANGE_N NO RANGE clause is comparable to the CASE_N NO CASE clause, and the UNKNOWN clause has the same meaning for both functions.
The Optimizer knows the number of partitions that are defined when RANGE_N is used as the partitioning expression.