[轉載] Interval Partitioning for Oracle 11g (new feature)

http://www.dba-oracle.com/t_interval_partitioning.htm
http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm

Interval Partitioning for Oracle 11g

可以將range partition table 改成自動切割range by date...

create table
pos_data_range (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
)
   PARTITION BY RANGE (start_date)
(
   PARTITION pos_data_p0 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
);

--設定區間為一個月
alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

--當然,也可以設定區間為三個月
alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));

--取消Interval Partitioning, 轉回traditional range partition table
alter table pos_data_range set INTERVAL();

其他限制如下:
Interval partitioning restrictions include:

• Index Organized tables (IOTs) are not supported by interval partitioning.
• Domain index cannot be created on interval partitioned tables.
• Only one partitioning key column can be set for the interval partition and it must be either a DATE or NUMBER data type.