oracle11g新特性 Interval Partition

  分区(Partition)一直是Oracle数据库引以为傲的一项技术，正是分区的存在让Oracle高效的处理海量数据成为可能，在Oracle 11g中，分区技术在易用性和可扩展性上再次得到了增强。在10g的Oracle版本中，要对分区表做调整，尤其是对RANGE分区添加新的分区都需要DBA手动定期添加，或都使用存储过程进行管理。在11G的版本中的Interval Partition不再需要DBA去干预新分区的添加，Oracle会自动去执行这样的操作，减少了DBA的工作量。Interval Partition是Range分区的一个扩展。

使用Interval Partition也有一些限制：

• You can only specify one partitioning key column, and it must be of NUMBER or DATE type.

• Interval partitioning is not supported for index-organized tables.

• You cannot create a domain index on an interval-partitioned table.

• Interval partitioning is not supported at the subpartition level. ...

 Interval Partition也可以创建复合分区：

• Interval-range

• Interval-hash

• Interval-list

 创建Interval分区表：

CREATE TABLE t
(
  id number,
  create_date  date
)
partition by range(create_date) interval(numtoyminterval(1,'MONTH'))
(
  partition p201303 values less than (date '2013-03-01'),
  partition p201304 values less than (date '2013-04-01'),
  partition p201305 values less than (date '2013-05-01')
); 

这里就创建了一个每个月会自动生成一个分区的表。

interval函数以及numtoninterval函数的说明

SQL> SQL>  select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_nam                 e = 'T' order by partition_position;

PARTITION_NAME                 INT HIGH_VALUE
------------------------------ --- --------------------------------------------------------------------------------                 -----
P201303                        NO  TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201304                        NO  TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201305                        NO  TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--可以发现interval=NO，目前是指定的3个分区。

接着插入一笔非这个指定分区的数据：

SQL> insert into t values(1 ,date'2013-05-11');

1 row created.

SQL> commit;

Commit complete.


SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;

PARTITION_NAME                 INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303                        NO  TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201304                        NO  TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201305                        NO  TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41                        YES TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

发现自动建立了一个新分区sys_p41,interval值为YES,说明这个是inteval分区，high_value是TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

试一下删除分区：

SQL> alter table t drop partition P201304;

Table altered.

 将P201304分区删除，这个分区的数据也会同时删除。

SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;

PARTITION_NAME                 INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303                        NO  TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201305                        NO  TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41                        YES TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> alter table t drop partition P201305;
alter table t drop partition P201305
                             *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

删除P201305的时候出错，提示最后一个range分区无法删除

那怎么删除这个分区？

步骤如下：

--先执行一下set interval(),使得interval的值都为NO
SQL> alter table t set interval (NUMTOYMINTERVAL(1,'month'));

Table altered.

SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;

PARTITION_NAME                 INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303                        NO  TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P201305                        NO  TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41                        NO  TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--再删除的时间就没有问题了

SQL> alter table t drop partition P201305;

Table altered.

--验证删除成功！

SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;

PARTITION_NAME                 INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303                        NO  TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P41                        NO  TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA