Oracle间隔分区

一、间隔分区的特点

1、由range分区派生而来

2、以定长宽度创建分区(比如年、月、具体的数字(比如100、500等))

3、分区字段必须是number或date类型

4、必须至少指定一个range分区(永久分区)

5、当有记录插入时，系统根据需要自动创建新的分区和本地索引

6、已有的范围分区可被转换成间隔分区(通过ALTER TABLE SET INTERVAL选项完成)

7、Interval Partitioning不支持支持索引组织表，也不能创建domain index

8、对于采用date类型的Interval Partitioning可以支持按year、month、day、hour、minute、second六种类型的定长宽度分区，分别通过如下函数转换:

numtoyminterval(n,{‘YEAR’|’MONTH’})

numtodsinterval(n,{‘DAY’|’HOUR’|’MINUTE’|’SECOND’})

9、对于采用number类型的Interval Partitioning必须按照固定的宽度分区

10、如果对分区需要单独存储，表也需要指定表空间，并且分区字段不能为空。

二、自动分区案例：

1、创建表空间：

create tablespace part

datafile ‘D:\app\part01.dbf’ size 1G;

2、按日创建分区

create table interval_test_by_day

( region number(4),

recdate date not null,

recoid  number

)

partition by range(recdate)

interval(numtodsinterval(1,’day’))

(

partition p1 values less than(to_date(‘20140327′,’yyyymmdd’)) tablespace part

storage

(

initial 1M

minextents 1

maxextents unlimited

)

);

select table_name,partition_name,high_value from user_tab_partitions

where table_name=’INTERVAL_TEST_BY_DAY’ order by partition_position;

select table_name,partitioning_type,status,interval from user_part_tables

where table_name=’INTERVAL_TEST_BY_DAY’;

insert into interval_test_by_day values(531,to_date(‘20141202′,’yyyymmdd’),1);

insert into interval_test_by_day values(531,to_date(‘20141203′,’yyyymmdd’),2);

insert into interval_test_by_day values(531,to_date(‘20141205′,’yyyymmdd’),3);

select table_name,partition_name,high_value from user_tab_partitions

where table_name=’INTERVAL_TEST_BY_DAY’ order by partition_position;

select table_name,partitioning_type,status,interval from user_part_tables

where table_name=’INTERVAL_TEST_BY_DAY’;

insert into interval_test_by_day values(531,to_date(‘20141204′,’yyyymmdd’),4);

select table_name,partition_name,high_value from user_tab_partitions

where table_name=’INTERVAL_TEST_BY_DAY’ order by partition_position;

select segment_name,PARTITION_NAME,tablespace_name from dba_extents where segment_name=’INTERVAL_TEST_BY_DAY’;

3、按月创建分区

create table interval_test_by_month

( region number(4),

recdate date not null,

recoid  number

)

tablespace part

partition by range(recdate)

interval(numtoyminterval(1,’month’))

(

partition p1 values less than(to_date(‘20140401′,’yyyymmdd’)) tablespace part

storage

(

initial 1M

minextents 1

maxextents unlimited

)

);

select table_name,partition_name,high_value from user_tab_partitions

where table_name=’INTERVAL_TEST_BY_MONTH’ order by partition_position;

select table_name,partitioning_type,status,interval from user_part_tables

where table_name=’INTERVAL_TEST_BY_MONTH’;

insert into interval_test_by_month values(531,to_date(‘20141212′,’yyyymmdd’),1);

insert into interval_test_by_month values(531,to_date(‘20140110′,’yyyymmdd’),2);

insert into interval_test_by_month values(531,to_date(‘20140601′,’yyyymmdd’),3);

select table_name,partition_name,high_value from user_tab_partitions

where table_name=’INTERVAL_TEST_BY_MONTH’ order by partition_position;

select segment_name,PARTITION_NAME,tablespace_name from dba_extents where segment_name=’INTERVAL_TEST_BY_MONTH’;

select * from interval_test_by_month partition (p1);

select * from interval_test_by_month partition (sys_p63);

select * from interval_test_by_month partition (sys_p64);

select * from interval_test_by_month partition (sys_p65);

4、按年分区

create table interval_test_by_year

(region number(4),

recdate date not null,

recoid  number

)

tablespace part

partition by range(recdate)

interval(numtoyminterval(1,’year’))

(

partition p1 values less than(to_date(‘20050101′,’yyyymmdd’)) tablespace part

storage

(

initial 1M

minextents 1

maxextents unlimited

)

);

select table_name,partition_name,high_value from user_tab_partitions

where table_name=’INTERVAL_TEST_BY_YEAR’ order by partition_position;

select table_name,partitioning_type,status,interval from user_part_tables

where table_name=’INTERVAL_TEST_BY_YEAR’;

select * from interval_test_by_year;

insert into interval_test_by_year values(531,to_date(‘20140101′,’yyyymmdd’),1);

insert into interval_test_by_year values(531,to_date(‘20150101′,’yyyymmdd’),2);

insert into interval_test_by_year values(531,to_date(‘20160101′,’yyyymmdd’),3);

select table_name,partition_name,high_value from user_tab_partitions

where table_name=’INTERVAL_TEST_BY_YEAR’ order by partition_position;

select * from interval_test_by_year;

select * from interval_test_by_year partition (p1);

select * from interval_test_by_year partition (sys_p61);

select * from interval_test_by_year partition (sys_p62);

5、按number进行分区,每2000一个档次

create table interval_test_by_number

(empno number(6),

name  varchar2(20),

sal number(6)

)

tablespace part

partition by range(sal)

Interval (2000)

(

partition p_2k values less than (2001) tablespace part

);

select table_name,partition_name,high_value from user_tab_partitions

where table_name=’INTERVAL_TEST_BY_NUMBER’ order by partition_position;

select table_name,partitioning_type,status,interval from user_part_tables

where table_name=’INTERVAL_TEST_BY_NUMBER’;

insert into interval_test_by_number values (1001,’thomas zhang’,1800);

insert into interval_test_by_number values (1002,’zhangrunping’,2500);

insert into interval_test_by_number values (1003,’zrp’,3000);

insert into interval_test_by_number values (1005,’CEO’,9999);

select table_name,partition_name,high_value from user_tab_partitions

where table_name=’INTERVAL_TEST_BY_NUMBER’order by partition_position;