什么是interval partition

11g推出的一项新特性，可以自动为范围分区表添加分区；一旦采用此功能，就不可手工添加分区了；

可以为interval partition指定多个表空间，oracle采用轮循的方式挨个使用每个表空间；

如何使用interval partition

测试环境11201

如下创建一个测试表

create table sales
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
(
partition p1107 values less than (to_date('2012-08-01','yyyy-mm-dd'))
);

如果试图插入12年8月1号以后的数据，则会报错ORA-14400: inserted partition key does not map to any partition

SQL> insert into sales values (1, to_date('2012-09-01','yyyy-mm-dd'));

insert into sales values (1, to_date('2009-09-01','yyyy-mm-dd'))

            *

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

将其改为interval 分区

SQL> alter table sales set interval(NUMTOYMINTERVAL(1,'month'));

Table altered.

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

此时表里还没有数据

SQL> insert into sales values (1, to_date('2012-09-01','yyyy-mm-dd'));

1 row created.

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P21         TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> rollback;

Rollback complete.

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P21         TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--此时数据可以成功插入该表，即便rollback后新分区依然存在

SQL> insert into sales values (1, to_date('2012-08-01','yyyy-mm-dd'));

1 row created.

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P22         TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P21         TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--先插入12年9月的数据，自动分配的分区名为sys_p21，如此时再插入12年8月的数据，对应的分区名则为sys_p22

--因此使用interval分区时，最好确保插入的数据是有序的，否则可能造成如上不便

管理维护interval-partitioned表

对于现有的interval-partitioned表，可以使用set interval()将其改回range-partitioned表；

也可使用set interval()改变其interval设置，首先将其变为range partitions，然后自动创建新的interval partitions

1 将interval-partitioned表改回range-partitioned表

SQL> select dbms_metadata.get_ddl('TABLE','SALES') from dual;

DBMS_METADATA.GET_DDL('TABLE','SALES')

--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."SALES"

   (    "SALES_ID" NUMBER,

        "SALES_DT" DATE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM"

  PARTITION BY RANGE ("SALES_DT") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

 (PARTITION "P1107"  VALUES LESS THAN (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM

-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM" NOCOMPRESS )

--将其变回range partition

SQL>  alter table sales set interval();

Table altered.

SQL> select dbms_metadata.get_ddl('TABLE','SALES') from dual;

DBMS_METADATA.GET_DDL('TABLE','SALES')

--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."SALES"

   (    "SALES_ID" NUMBER,

        "SALES_DT" DATE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM"

  PARTITION BY RANGE ("SALES_DT")

 (PARTITION "P1107"  VALUES LESS THAN (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM

-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM" NOCOMPRESS ,

 PARTITION "SYS_P22"  VALUES LESS THAN (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-M

M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_

2 改变现有表的interval

--

SQL> alter table sales set interval(NUMTODSINTERVAL(7,'day'));

Table altered.

SQL> insert into sales values (4,to_date('2012-08-07','yyyy-mm-dd'));

1 row created.

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P22         TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P21         TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SQL> insert into sales values (4,to_date('2012-10-01','yyyy-mm-dd'));

1 row created.

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P22         TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P21         TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P23         TO_DATE(' 2012-10-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

--即使改变现有表的interval，以及存在的范围分区也不会被修改