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'> 2、按日创建分区
create table interval_test_by_day
( region number(4),
recdate date not null,
recoidnumber
)
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,
recoidnumber
)
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,
recoidnumber
)
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),
namevarchar2(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;
页:
[1]