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' size 10m autoextend on;
create tablespace tablespace2 datafile '+DATA' size 10m autoextend on;
create tablespace tablespace3 datafile '+DATA' size 10m autoextend on;
create tablespace tablespace4 datafile '+DATA' size 10m autoextend on;
create tablespace tablespace5 datafile '+DATA' size 10m autoextend on;
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> alter table cai.intervalpart3 set INTERVAL(100);
Table altered.
--插入成功
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> alter table cai.intervalpart3 set INTERVAL(300);
Table altered.
--插入数据
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> alter table cai.intervalpart3 set INTERVAL();
Table altered.
--插入数据不成功
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;