Oracle Interval-partition solves the big problem with range partitioning

The blog Oracle partition describes several partitions of Oracle and gives examples of typical Oracle partitions such as range partitions and list partitions.

When you actually use the range partition, you encounter this dilemma:

Createtabletmp_lxq_1

(

Proposalno VARCHAR2 (22),

StartDate DATE

)

Partitionbyrange (StartDate) (

Partitionpart_t01values less than (to_date (' 2008-01-01 ', ' yyyy-mm-dd ')),

Partitionpart_t02values less than (to_date (' 2009-01-01 ', ' yyyy-mm-dd ')),

Partitionpart_t03values less than (to_date (' 2010-01-01 ', ' yyyy-mm-dd ')),

Partitionpart_t04values less than (to_date (' 2011-01-01 ', ' yyyy-mm-dd ')),

Partitionpart_t05values less than (to_date (' 2012-01-01 ', ' yyyy-mm-dd ')),

Partitionpart_t06values less than (to_date (' 2013-01-01 ', ' yyyy-mm-dd ')),

Partitionpart_t07values less Than (MaxValue)

);

The time in this case is only in the year, and then the time after the year is completely put into the MaxValue , which is indeed a solution, that is, to remove 2008-2013 years of data, The database does not error. But this changes the original purpose of our partition, which is to make the data in each part balanced to speed up the query.

This is often the case in actual work before oracle11g occurs, and it is usually done manually by the DBA or the developer, or by defining the MaxValue directly.

Oracle 11g new features Interval Partitioning can solve this problem, the following describes the Interval partition.

One,interval partition

The interval partition is a new feature of oracle11g, which is a function extension for the Range type partition. For a Range partition of a continuous data type , if the new data values are inserted that do not match the current partition, theinterval-partition attribute enables automatic partition creation.

Example:

CreateTable tmp_lxq_1

(

Proposalnovarchar2 (22),

Startdatedate

)

Partitionby Range (StartDate)

Interval (numtoyminterval (1, ' Year ')) (

PARTITIONPART_T01 values less than (to_date (' 2008-01-01 ', ' yyyy-mm-dd ')),

partitionpart_t02 values less than (to_date (' 2009-01-01 ', ' yyyy-mm-dd ')),

partitionpart_t03 values less than (to_date (' 2010-01-01 ', ' yyyy-mm-dd ')),

partitionpart_t04 values less than (to_date (' 2011-01-01 ', ' yyyy-mm-dd ')),

partitionpart_t05 values less than (to_date (' 2012-01-01 ', ' yyyy-mm-dd ')),

partitionpart_t06 values less than (to_date (' 2013-01-01 ', ' yyyy-mm-dd '))

);

If the value of the year is inserted, the system automatically adds a partition with a partition range of 2014-01-01 to 2014-12-31 days.

Ii. Conversion of interval partition and range partition

Altertable tmp_lxq_1 SET INTERVAL (numtoyminterval (1, ' year '));

For INTERVAL partitioned tables, new data that exceeds the upper partition limit will automatically cause the corresponding INTERVAL partition to be created.

The same INTERVAL partition table can be easily converted into a RANGE partition table, just do not enter the value of INTERVAL:

ALTER TABLEtmp_lxq_1 SET INTERVAL ();

Three, the characteristics of interval partition

1. derived from the range partition

2. create partitions with fixed-length widths (such as year, month, and specific numbers (e.g. ,

3. the partition field must be number or date type

4. You must specify at least one range partition (permanent partition)

5. when a record is inserted, the system automatically creates new partitions and local indexes as needed

6. an existing range partition can be converted to an interval partition ( completed by the ALTER TABLE SET INTERVAL option)

7.intervalpartitioning does not support indexed organization tables

8. the domain index cannot be created on the Interval partitioning table

Iv.interval zoning issues

When Oracle automatically creates the partition, the system will specify a partition name by default, and the partition name created by default will have a certain gap with our partition naming specification, which is not known for a moment.

Oracle Interval-partition solves the big problem with range partitioning