【转】Oracle 11g 新特性 -- INTERVAL分区
Interval 分区
在Oracle Database 11g中可以创建新类型的Interval分区表，Interval类型分区表，可以根据加载数据，自动创建指定间隔的分区。
创建按月分区的分区表：
1. 创建分区表
1 /* Formatted on 2010/6/10 20:21:12 (QP5 v5.115.810.9015) */ 2 CREATE TABLE intervalpart (c1 NUMBER, c3 DATE) 3 PARTITION BY RANGE (c3) 4 INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') ) 5 (PARTITION part1 6 VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')), 7 PARTITION part2 8 VALUES LESS THAN (TO_DATE ('02/12/2010', 'MM/DD/YYYY')) 9 );
注意：如果在建Interval分区表是没有把所有的分区写完成，在插入相关数据后会自动生成分区
2. 查看现在表的分区：
1 SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALPART'; 2 3 TABLE_NAME PARTITION_NAME 4 ------------------------------ ------------------------------ 5 INTERVALPART PART1 6 INTERVALPART PART2
3. 插入测试数据：
1 SQL> begin 2 for i in 0 .. 11 loop 3 insert into intervalpart values(i,add_months(to_date('2010-1-1','yyyy-mm-dd'),i)); 4 end loop ; 5 commit; 6 end; 7 / 8 PL/SQL 过程已成功完成。
补充：add_months()函数获取前一个月或者下一个月的月份， 参数中 负数 代表 往前， 正数 代表 往后。
--上一个月
select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;
--下一个月
select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;
4. 观察自动创建的分区：
1 SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALPART'; 2 3 TABLE_NAME PARTITION_NAME 4 ------------------------------ ------------------------------ 5 INTERVALPART PART1 6 INTERVALPART PART2 7 INTERVALPART SYS_P22 8 INTERVALPART SYS_P23 9 INTERVALPART SYS_P24 10 INTERVALPART SYS_P25 11 INTERVALPART SYS_P26 12 INTERVALPART SYS_P27 13 INTERVALPART SYS_P28 14 INTERVALPART SYS_P29 15 INTERVALPART SYS_P30 16 INTERVALPART SYS_P31 17 18 已选择12行。
5. 查看分区内容：
SQL> select * from INTERVALPART; C1 C3 ---------- ---------- 1 2010-01-01 0 2010-01-01 1 2010-02-01 2 2010-03-01 3 2010-04-01 4 2010-05-01 5 2010-06-01 6 2010-07-01 7 2010-08-01 8 2010-09-01 9 2010-10-01 10 2010-11-01 11 2010-12-01 已选择13行。
1 SQL> select * from INTERVALPART partition(part1); 2 3 C1 C3 4 ---------- ---------- 5 1 2010-01-01 6 0 2010-01-01 7 8 SQL> select * from INTERVALPART partition(part2); 9 10 C1 C3 11 ---------- ---------- 12 1 2010-02-01
下面创建一个以天为间隔的分区表：
1. 创建分区表：
1 SQL> create table dave 2 ( 3 id number, 4 dt date 5 ) 6 partition by range (dt) 7 INTERVAL (NUMTODSINTERVAL(1,'day')) 8 ( 9 partition p100101 values less than (to_date('2010-01-01','yyyy-mm-dd')) 10 );
2. 查看表分区：
1 SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE'; 2 3 TABLE_NAME PARTITION_NAME 4 ------------------------------ ------------------------------ 5 DAVE P100101
3. 插入测试数据：
1 SQL> begin 2 for i in 1 .. 12 loop 3 insert into dave values(i,trunc(to_date('2010-1-1','yyyy-mm-dd')+i)); 4 end loop; 5 commit; 6 end; 7 / 8 PL/SQL 过程已成功完成。
4. 观察自动创建的分区：
1 SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE'; 2 3 TABLE_NAME PARTITION_NAME 4 ------------------------------ ------------------------------ 5 DAVE P100101 6 DAVE SYS_P32 7 DAVE SYS_P33 8 DAVE SYS_P34 9 DAVE SYS_P35 10 DAVE SYS_P36 11 DAVE SYS_P37 12 DAVE SYS_P38 13 DAVE SYS_P39 14 DAVE SYS_P40 15 DAVE SYS_P41 16 DAVE SYS_P42 17 DAVE SYS_P43 18 19 已选择13行。
5. 查看分区内容：
1 SQL> select * from dave partition(SYS_P32); 2 3 ID DT 4 ---------- ---------- 5 1 2010-01-02 6 7 SQL> select * from dave partition(SYS_P33); 8 9 ID DT 10 ---------- ---------- 11 2 2010-01-03 12 13 SQL> select * from dave partition(SYS_P34); 14 15 ID DT 16 ---------- ---------- 17 3 2010-01-04 18 19 SQL> select * from dave; 20 21 ID DT 22 ---------- ---------- 23 1 2010-01-02 24 2 2010-01-03 25 3 2010-01-04 26 4 2010-01-05 27 5 2010-01-06 28 6 2010-01-07 29 7 2010-01-08 30 8 2010-01-09 31 9 2010-01-10 32 10 2010-01-11 33 11 2010-01-12 34 12 2010-01-13 35 36 已选择12行。
我们对Interval分区 进行一个总结，通过上面的2个例子我们发现一个现象，就是在创建分区的时候，如果选择了Interval分区，那么在该分区表中，没有创建的分区会自动生成。 这样有什么好处呢？
我们假设一下，由于数据量的巨大，所以表设计为每天一个分区，数据库管理员日常要做的一件重复而无聊的工作就是每隔一天要生成新的分区，用以存储第二天的数据。如果在Oracle 11g，我们就可以用Interval分区很好的实现这个功能，Oracle会自动完成剩下的分区，是不是很方便呢？
