ORACLE 区间分区(Interval Partitioning)

filts

　　11g在分区上有了很大的改进，其中有一个特性是Interval-Partition，他是range分区的派生，自动创建指定间隔的分区。
　　在 11g 之前，如果数据中出现未能匹配分区条件的情况，系统会拒绝进行数据操作。
　　对时间类型和数字类型的支持：
　　1.对于采用date类型或TIMESTAMP类型的Interval Partitioning可以支持按year、month、day、hour、minute、second六种类型的定长宽度分区，分别通过如下函数转换
　　numtoyminterval ( n, { 'YEAR'|'MONTH'})
　　numtodsinterval ( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})
　　2. Interval (number) 即多少值一个分区
　　创建示例
　　1.时间类型
　　--创建表
　　SQL> CREATE TABLE cai.intervalpart1 (c1 NUMBER, c2 DATE)
　　2  PARTITION BY RANGE (c2)
　　3     INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
　　4     (PARTITION part1
　　5         VALUES LESS THAN (TO_DATE ('02/01/2013', 'MM/DD/YYYY'))
　　6    );
　　Table created.
　　--查询只有一个分区
　　SQL> select table_name, partition_name, HIGH_VALUE
　　2    from dba_tab_partitions t
　　3   where table_name = 'INTERVALPART1';
　　TABLE_NAME       PARTITION_NAME           HIGH_VALUE
　　---------------- -----------------         -------------------------------------------------------
　　INTERVALPART1       PART1                 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
　　--增加2行记录一行是2月份的，一行是1月份的
　　SQL>   insert into cai.intervalpart1 values (1,TO_DATE ('02/02/2013', 'MM/DD/YYYY'));
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　SQL> insert into cai.intervalpart1 values (1,TO_DATE ('01/01/2013', 'MM/DD/YYYY'));
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　SQL>
　　SQL>   Select Count(1) From cai.intervalpart1;
　　COUNT(1)
　　----------
　　2
　　--增加了2月份的分区
　　SQL>
　　SQL> select table_name, partition_name, HIGH_VALUE
　　2    from dba_tab_partitions t
　　3   where table_name = 'INTERVALPART1';
　　TABLE_NAME      PARTITION_NAME    HIGH_VALUE
　　-------------- ----------------- -------------------------------------------------------------
　　INTERVALPART1        PART1        TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
　　INTERVALPART1        SYS_P105     TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
　　--插入当前时间的分区
　　SQL>  insert into cai.intervalpart1 values (1,sysdate);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　--又有一个分区新建了，如果插入的时间在两个月以后或者更久，则Oracle只生成必须的分区，并不会生成连续分区。
　　SQL> select table_name, partition_name, HIGH_VALUE
　　2    from dba_tab_partitions t
　　3   where table_name = 'INTERVALPART1';
　　TABLE_NAME         PARTITION_NAME    HIGH_VALUE
　　----------------- ---------------    --------------------------------------------------------------------------------
　　INTERVALPART1      PART1             TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
　　INTERVALPART1      SYS_P105          TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
　　INTERVALPART1      SYS_P106          TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
　　SQL>
　　配置多个表空间的分区存储 --会循环不断往这几个表空间插入（随机插入某个表空间）

　　create  tablespace tablespace1 datafile '+DATA'>
　　create  tablespace tablespace2 datafile '+DATA'>
　　create  tablespace tablespace3 datafile '+DATA'>
　　create  tablespace tablespace4 datafile '+DATA'>
　　create  tablespace tablespace5 datafile '+DATA'>　　alter table cai.intervalpart1 set STORE IN (tablespace1,tablespace2,tablespace3,tablespace4,tablespace5);
　　insert into cai.intervalpart1 values (1,TO_DATE ('04/04/2013', 'MM/DD/YYYY'));
　　insert into cai.intervalpart1 values (1,TO_DATE ('05/05/2013', 'MM/DD/YYYY'));
　　insert into cai.intervalpart1 values (1,TO_DATE ('06/06/2013', 'MM/DD/YYYY'));
　　insert into cai.intervalpart1 values (1,TO_DATE ('07/07/2013', 'MM/DD/YYYY'));
　　insert into cai.intervalpart1 values (1,TO_DATE ('08/08/2013', 'MM/DD/YYYY'));
　　insert into cai.intervalpart1 values (1,TO_DATE ('09/08/2013', 'MM/DD/YYYY'));
　　insert into cai.intervalpart1 values (1,TO_DATE ('10/08/2013', 'MM/DD/YYYY'));
　　SQL> select table_name, partition_name, HIGH_VALUE,TABLESPACE_NAME
　　2   from dba_tab_partitions t
　　3   where table_name = 'INTERVALPART1';
　　TABLE_NAME      PARTITION_NAME       HIGH_VALUE                                                                                           TABLESPACE_NAME
　　--------------- -------------------- ---------------------------------------------------------------------------------------------------- ------------------
　　INTERVALPART1   SYS_P908             TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                     TABLESPACE5
　　INTERVALPART1   SYS_P907             TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                     TABLESPACE4
　　INTERVALPART1   SYS_P906             TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                     TABLESPACE3
　　INTERVALPART1   SYS_P905             TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                     TABLESPACE2
　　INTERVALPART1   SYS_P904             TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                     TABLESPACE1
　　INTERVALPART1   SYS_P903             TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                     TABLESPACE5
　　INTERVALPART1   SYS_P902             TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                     TABLESPACE4
　　INTERVALPART1   SYS_P901             TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                     USERS
　　INTERVALPART1   SYS_P881             TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                     USERS
　　INTERVALPART1   PART1                TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                     USERS
　　2.数字类型
　　----创建表
　　SQL> CREATE TABLE cai.intervalpart2 (c1 NUMBER, c2 DATE)
　　2   PARTITION BY RANGE (c1)
　　3     INTERVAL (100)
　　4      (PARTITION part1
　　5          VALUES LESS THAN (100)
　　6    );
　　Table created.
　　--查询只有一个分区
　　SQL> select table_name, partition_name, HIGH_VALUE
　　2     from dba_tab_partitions t
　　3    where table_name = 'INTERVALPART2';
　　TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
　　------------------------------ ------------------------------ -------------------------
　　INTERVALPART2                  PART1                          100
　　SQL>
　　--增加2行记录一行是1，一行是101
　　SQL> insert into  cai.intervalpart2 values (1,sysdate);
　　1 row created.
　　SQL> insert into  cai.intervalpart2 values (101,sysdate);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　SQL>
　　SQL>  Select Count(1) From  cai.intervalpart2;
　　COUNT(1)
　　----------
　　2
　　--增加了101的分区
　　SQL> select table_name, partition_name, HIGH_VALUE
　　2  from dba_tab_partitions t
　　3  where table_name = 'INTERVALPART2';
　　TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
　　------------------------------ ------------------------------ --------------------------
　　INTERVALPART2                  PART1                          100
　　INTERVALPART2                  SYS_P107                       200
　　--插入1101数值的分区
　　SQL>  insert into  cai.intervalpart2 values (1101,sysdate);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　--又有一个分区新建了，如果插入的数值是差别很大或者更久，则Oracle只生成必须的分区，并不会生成连续分区。
　　SQL> select table_name, partition_name, HIGH_VALUE
　　2  from dba_tab_partitions t
　　3  where table_name = 'INTERVALPART2';
　　TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
　　------------------------------ ------------------------------ ---------------------------------------
　　INTERVALPART2                  PART1                          100
　　INTERVALPART2                  SYS_P107                       200
　　INTERVALPART2                  SYS_P108                       1200
　　管理：
　　1.自动分区与自动分区的转换
　　--创建普通分区表
　　SQL> CREATE TABLE cai.intervalpart3 (c1 NUMBER, c2 DATE)
　　2         PARTITION BY RANGE (c1)
　　3         (PARTITION part1 VALUES LESS THAN (100)
　　4  );
　　Table created.
　　SQL>  insert into  cai.intervalpart3 values (1,sysdate);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　--当插入超出值时会报错
　　SQL>  insert into  cai.intervalpart3 values (101,sysdate);
　　insert into  cai.intervalpart3 values (101,sysdate)
　　*
　　ERROR at line 1:
　　ORA-14400: inserted partition key does not map to any partition
　　--设置100数值为分区

　　SQL>>
　　Table>　　--插入成功
　　SQL> insert into  cai.intervalpart3 values (101,sysdate);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　--新生成分区
　　SQL> select table_name, partition_name, HIGH_VALUE
　　2    from dba_tab_partitions t
　　3   where table_name = 'INTERVALPART3';
　　TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
　　------------------------------ ------------------------------ ----------------------------------
　　INTERVALPART3                  PART1                          100
　　INTERVALPART3                  SYS_P109                       200
　　SQL>
　　SQL>
　　--也可以设置个更高的值

　　SQL>>
　　Table>　　--插入数据
　　SQL> insert into  cai.intervalpart3 values (201,sysdate);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　SQL>  insert into  cai.intervalpart3 values (401,sysdate);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　SQL> insert into  cai.intervalpart3 values (501,sysdate);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　SQL> insert into  cai.intervalpart3 values (601,sysdate);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　--更改的值生效，之前的分区不影响。
　　SQL> select table_name, partition_name, HIGH_VALUE
　　2    from dba_tab_partitions t
　　3   where table_name = 'INTERVALPART3';
　　TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
　　------------------------------ ------------------------------ ----------------------
　　INTERVALPART3                  PART1                          100
　　INTERVALPART3                  SYS_P109                       200
　　INTERVALPART3                  SYS_P110                       500
　　INTERVALPART3                  SYS_P111                       800
　　SQL>
　　--关闭自动分区

　　SQL>>
　　Table>　　--插入数据不成功
　　SQL>  insert into  cai.intervalpart3 values (801,sysdate);
　　insert into  cai.intervalpart3 values (801,sysdate)
　　*
　　ERROR at line 1:
　　ORA-14400: inserted partition key does not map to any partition
　　2.配置多个表空间的分区存储
　　alter table cai.intervalpart3 set STORE IN (tablespace1, tablespace2, tablespace3);
　　这3个表空间，分区会循环分配到这3个表空间。
　　3. rename 分区名
　　分区：
　　alter table cai.INTERVALPART3  rename partition SYS_P109 to part2;
　　子分区：
　　alter table cai.INTERVALPART3  rename partition SYS_P109 to part2;