About
A range partitioning where the database automatically creates partitions for a specified interval.
Articles Related
Prerequisites
- At least one range partition using the PARTITION clause.
- The partitioning key can only be a single column name from the table
- The single partitioning key column must be of NUMBER or DATE type.
- The single partitioning key column can not contains NULL (you can specify it either)
- The table is not an index-organized tables.
- The partitioning keys must be in the primary key if the primary key is local.
A virtual column can be used to partition on a transformed column value
The max number of partition is 1 Million, and for an integer column partition key:
<MATH> \frac{Max(integerColumn)}{Interval} < 1 M </MATH>
Syntax
See Reference
where:
- partition: partition name. Example: “20110701”
Concept
Transition point
The high value of the range partitions is the transition point. The database automatically creates interval partitions for data beyond that transition point.
Example: the transition point is 1-1-2010 because 1-1-2010 is the max date.
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
- The high bound of partition p3 represents the transition point. p3
- All partitions below the transition point (p0, p1, and p2 in this example) are in the range section
- All partitions above the transition point fall into the interval section.
Interval
The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table.
Example: the interval is one month (NUMTOYMINTERVAL(1, 'MONTH'))
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
See function:
- apps/search/search.jsp for partition by month, year
- apps/search/search.jsp for partition by 'DAY', 'HOUR', 'MINUTE', 'SECOND'
Boundary
The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.
Example:
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
- the lower boundary for the January 2010 interval is January 1, 2010.
- the lower boundary for the July 2010 interval is July 1, 2010, regardless of whether the June 2010 partition was previously created.
Out of bound example: using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.
Storage
The optional STORE IN clause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.
Management
Change the Interval
to set a new interval for the table
ALTER TABLE t_part SET INTERVAL(NUMTODSINTERVAL(1,'DAY'));
Disable / turn off interval partitioning
You can disable interval partitioning with the following statement:
ALTER TABLE <table name> SET INTERVAL ();
Example
An interval partitioned table with monthly intervals where above the transition_point of January 1, 2010, partitions are created with a width of one month.
CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
Documentation / Reference
- INTERVAL RANGE Partition Giving ORA-14400 (Doc ID 1081230.1) for the max partition clause of 1 Million