分区表的创建和使用（1）



Oracle 10g提供了以下几种分区类型：

       （1）范围分区（range）；

       （2）哈希分区（hash）；

       （3）列表分区（list）；

       （4）范围－哈希复合分区（range-hash）；

       （5）范围－列表复合分区（range-list）。

分区提供以下优点：

       （1）由于将数据分散到各个分区中，减少了数据损坏的可能性；

       （2）可以对单独的分区进行备份和恢复；

       （3）可以将分区映射到不同的物理磁盘上，来分散IO；

       （4）提高可管理性、可用性和性能。

官网建议使用分区的两种情况

（1）Tables greater than 2GB should always be considered for partitioning.

（2）Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.



一、范围分区
创建一个按字段(支持多个字段)数据范围分区的表，分区置于指定的不同表空间中
如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。


示例代码：

--为各个分区准备独立的表空间
create tablespace tp01 datafile '/home/oracle/oradata/c1/tp01_01.dbf' size 50m;

create tablespace tp02 datafile '/home/oracle/oradata/c1/tp02_01.dbf' size 50m;

create tablespace tp03 datafile '/home/oracle/oradata/c1/tp03_01.dbf' size 50m;

create tablespace tp04 datafile '/home/oracle/oradata/c1/tp04_01.dbf' size 50m;

create tablespace tp05 datafile '/home/oracle/oradata/c1/tp05_01.dbf' size 50m;

create tablespace tp06 datafile '/home/oracle/oradata/c1/tp06_01.dbf' size 50m;


create table info  
(  
id int,
name varchar2(32),
age int  check(age<=200)
)  
partition by range(age)  
(  
  partition tp01 values less than(30)  tablespace tp01,  
  partition tp02 values less than(60)  tablespace tp02,
  partition tp03 values less than(90)  tablespace tp03,
  partition tp04 values less than(200) tablespace tp04
);

insert into info values(1,'fishcat1',18);
insert into info values(2,'fishcat2',30);
insert into info values(3,'fishcat3',40);
insert into info values(4,'fishcat4',60);
insert into info values(5,'fishcat5',80);
insert into info values(6,'fishcat6',150);
insert into info values(7,'fishcat7',250);


select * from info partition(tp01);

select * from info partition(tp02);

select * from info partition(tp03);

select * from info partition(tp04);

二、哈希分区
哈希分区是根据字段(支持多个字段)的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等。

create table info1
(  
id int,
name varchar2(32),
age int  check(age<=200)
)  
partition by hash(id)  
(  
  partition tp01  tablespace tp01,  
  partition tp02  tablespace tp02,
  partition tp03  tablespace tp03,
  partition tp04  tablespace tp04
);

insert into info1 values(1,'fishcat1',18);
insert into info1 values(2,'fishcat2',30);
insert into info1 values(3,'fishcat3',40);
insert into info1 values(4,'fishcat4',60);
insert into info1 values(5,'fishcat5',80);
insert into info1 values(6,'fishcat6',150);
insert into info1 values(7,'fishcat7',250);
insert into info1 values(8,'fishcat1',18);
insert into info1 values(9,'fishcat2',30);
insert into info1 values(10,'fishcat3',40);
insert into info1 values(11,'fishcat4',60);
insert into info1 values(12,'fishcat5',80);
insert into info1 values(13,'fishcat6',150);
insert into info1 values(14,'fishcat7',200);
insert into info1 values(15,'fishcat6',150);
insert into info1 values(16,'fishcat7',200);


select * from info1 partition(tp01);

select * from info1 partition(tp02);

select * from info1 partition(tp03);

select * from info1 partition(tp04);


三、列表分区
列表分区明确指定了根据某字段(不支持多个字段,但支持一个字段的多个值)的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。
在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，
因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。
create table info2
(  
id int,
name varchar2(32),
age int  check(age<=200),
sex char(8)
)  
partition by list(sex)  
(  
  partition tp01 values('w')  tablespace tp01,  
  partition tp02 values('m')  tablespace tp02,
  partition tp03 values(default)  tablespace tp03
);

insert into info2 values(1,'fishcat1',18,'w');
insert into info2 values(2,'fishcat2',30,'m');
insert into info2 values(3,'fishcat3',40,'o');
insert into info2 values(4,'fishcat4',60,'w');
insert into info2 values(5,'fishcat5',80,'m');
insert into info2 values(6,'fishcat6',150,'o');



select * from info2 partition(tp01);

select * from info2 partition(tp02);

select * from info2 partition(tp03);

四、范围－哈希复合分区

根分区只能是range分区

create table info3  
(  
id int,
name varchar2(32),
age int  check(age<=200)
)  
partition by range(age)
subpartition by hash(id)
(  
  partition tp01 values less than(60)      (subpartition stp01  tablespace tp01,subpartition stp02  tablespace tp02),
  partition tp02 values less than(maxvalue)(subpartition stp03  tablespace tp03,subpartition stp04  tablespace tp04)
);

insert into info3 values(1,'fishcat1',18);
insert into info3 values(2,'fishcat2',30);
insert into info3 values(3,'fishcat3',40);
insert into info3 values(4,'fishcat4',60);
insert into info3 values(5,'fishcat5',80);
insert into info3 values(6,'fishcat6',150);
insert into info3 values(7,'fishcat7',250);
insert into info3 values(8,'fishcat1',18);
insert into info3 values(9,'fishcat2',30);
insert into info3 values(10,'fishcat3',40);
insert into info3 values(11,'fishcat4',60);
insert into info3 values(12,'fishcat5',80);
insert into info3 values(13,'fishcat6',150);
insert into info3 values(14,'fishcat7',200);
insert into info3 values(15,'fishcat6',150);
insert into info3 values(16,'fishcat7',200);



select * from info3 partition(tp01);

select * from info3 partition(tp02);

select * from info3 subpartition(stp01);

select * from info3 subpartition(stp02);

select * from info3 subpartition(stp03);

select * from info3 subpartition(stp04);


五、范围－列表复合分区

根分区只能是range分区

create table info4  
(  
id int,
name varchar2(32),
age int  check(age<=200),
sex char(8)
)  
partition by range(age)
subpartition by list(sex)
(  
  partition tp01 values less than(60)      
  (subpartition stp01 values('w')  tablespace tp01,
   subpartition stp02 values('m')  tablespace tp02,
   subpartition stp03 values('o')  tablespace tp03),
  partition tp02 values less than(maxvalue)
  (subpartition stp04 values('w') tablespace tp04,
   subpartition stp05 values('m') tablespace tp05,
   subpartition stp06 values('o') tablespace tp06)
);


insert into info4 values(1,'fishcat1',18,'w');
insert into info4 values(2,'fishcat2',30,'m');
insert into info4 values(3,'fishcat3',40,'o');
insert into info4 values(4,'fishcat4',60,'w');
insert into info4 values(5,'fishcat5',80,'m');
insert into info4 values(6,'fishcat6',150,'o');


select * from info4 partition(tp01);

select * from info4 partition(tp02);

select * from info4 subpartition(stp01);

select * from info4 subpartition(stp02);

select * from info4 subpartition(stp03);

select * from info4 subpartition(stp04);

select * from info4 subpartition(stp05);

select * from info4 subpartition(stp06);

分区表的创建和使用（1）



Oracle 10g提供了以下几种分区类型：

       （1）范围分区（range）；

       （2）哈希分区（hash）；

       （3）列表分区（list）；

       （4）范围－哈希复合分区（range-hash）；

       （5）范围－列表复合分区（range-list）。

分区提供以下优点：

       （1）由于将数据分散到各个分区中，减少了数据损坏的可能性；

       （2）可以对单独的分区进行备份和恢复；

       （3）可以将分区映射到不同的物理磁盘上，来分散IO；

       （4）提高可管理性、可用性和性能。

官网建议使用分区的两种情况

（1）Tables greater than 2GB should always be considered for partitioning.

（2）Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.



一、范围分区
创建一个按字段(支持多个字段)数据范围分区的表，分区置于指定的不同表空间中
如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。


示例代码：

--为各个分区准备独立的表空间
create tablespace tp01 datafile '/home/oracle/oradata/c1/tp01_01.dbf' size 50m;

create tablespace tp02 datafile '/home/oracle/oradata/c1/tp02_01.dbf' size 50m;

create tablespace tp03 datafile '/home/oracle/oradata/c1/tp03_01.dbf' size 50m;

create tablespace tp04 datafile '/home/oracle/oradata/c1/tp04_01.dbf' size 50m;

create tablespace tp05 datafile '/home/oracle/oradata/c1/tp05_01.dbf' size 50m;

create tablespace tp06 datafile '/home/oracle/oradata/c1/tp06_01.dbf' size 50m;


create table info  
(  
id int,
name varchar2(32),
age int  check(age<=200)
)  
partition by range(age)  
(  
  partition tp01 values less than(30)  tablespace tp01,  
  partition tp02 values less than(60)  tablespace tp02,
  partition tp03 values less than(90)  tablespace tp03,
  partition tp04 values less than(200) tablespace tp04
);

insert into info values(1,'fishcat1',18);
insert into info values(2,'fishcat2',30);
insert into info values(3,'fishcat3',40);
insert into info values(4,'fishcat4',60);
insert into info values(5,'fishcat5',80);
insert into info values(6,'fishcat6',150);
insert into info values(7,'fishcat7',250);


select * from info partition(tp01);

select * from info partition(tp02);

select * from info partition(tp03);

select * from info partition(tp04);

二、哈希分区
哈希分区是根据字段(支持多个字段)的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等。

create table info1
(  
id int,
name varchar2(32),
age int  check(age<=200)
)  
partition by hash(id)  
(  
  partition tp01  tablespace tp01,  
  partition tp02  tablespace tp02,
  partition tp03  tablespace tp03,
  partition tp04  tablespace tp04
);

insert into info1 values(1,'fishcat1',18);
insert into info1 values(2,'fishcat2',30);
insert into info1 values(3,'fishcat3',40);
insert into info1 values(4,'fishcat4',60);
insert into info1 values(5,'fishcat5',80);
insert into info1 values(6,'fishcat6',150);
insert into info1 values(7,'fishcat7',250);
insert into info1 values(8,'fishcat1',18);
insert into info1 values(9,'fishcat2',30);
insert into info1 values(10,'fishcat3',40);
insert into info1 values(11,'fishcat4',60);
insert into info1 values(12,'fishcat5',80);
insert into info1 values(13,'fishcat6',150);
insert into info1 values(14,'fishcat7',200);
insert into info1 values(15,'fishcat6',150);
insert into info1 values(16,'fishcat7',200);


select * from info1 partition(tp01);

select * from info1 partition(tp02);

select * from info1 partition(tp03);

select * from info1 partition(tp04);


三、列表分区
列表分区明确指定了根据某字段(不支持多个字段,但支持一个字段的多个值)的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。
在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，
因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。
create table info2
(  
id int,
name varchar2(32),
age int  check(age<=200),
sex char(8)
)  
partition by list(sex)  
(  
  partition tp01 values('w')  tablespace tp01,  
  partition tp02 values('m')  tablespace tp02,
  partition tp03 values(default)  tablespace tp03
);

insert into info2 values(1,'fishcat1',18,'w');
insert into info2 values(2,'fishcat2',30,'m');
insert into info2 values(3,'fishcat3',40,'o');
insert into info2 values(4,'fishcat4',60,'w');
insert into info2 values(5,'fishcat5',80,'m');
insert into info2 values(6,'fishcat6',150,'o');



select * from info2 partition(tp01);

select * from info2 partition(tp02);

select * from info2 partition(tp03);

四、范围－哈希复合分区

根分区只能是range分区

create table info3  
(  
id int,
name varchar2(32),
age int  check(age<=200)
)  
partition by range(age)
subpartition by hash(id)
(  
  partition tp01 values less than(60)      (subpartition stp01  tablespace tp01,subpartition stp02  tablespace tp02),
  partition tp02 values less than(maxvalue)(subpartition stp03  tablespace tp03,subpartition stp04  tablespace tp04)
);

insert into info3 values(1,'fishcat1',18);
insert into info3 values(2,'fishcat2',30);
insert into info3 values(3,'fishcat3',40);
insert into info3 values(4,'fishcat4',60);
insert into info3 values(5,'fishcat5',80);
insert into info3 values(6,'fishcat6',150);
insert into info3 values(7,'fishcat7',250);
insert into info3 values(8,'fishcat1',18);
insert into info3 values(9,'fishcat2',30);
insert into info3 values(10,'fishcat3',40);
insert into info3 values(11,'fishcat4',60);
insert into info3 values(12,'fishcat5',80);
insert into info3 values(13,'fishcat6',150);
insert into info3 values(14,'fishcat7',200);
insert into info3 values(15,'fishcat6',150);
insert into info3 values(16,'fishcat7',200);



select * from info3 partition(tp01);

select * from info3 partition(tp02);

select * from info3 subpartition(stp01);

select * from info3 subpartition(stp02);

select * from info3 subpartition(stp03);

select * from info3 subpartition(stp04);


五、范围－列表复合分区

根分区只能是range分区

create table info4  
(  
id int,
name varchar2(32),
age int  check(age<=200),
sex char(8)
)  
partition by range(age)
subpartition by list(sex)
(  
  partition tp01 values less than(60)      
  (subpartition stp01 values('w')  tablespace tp01,
   subpartition stp02 values('m')  tablespace tp02,
   subpartition stp03 values('o')  tablespace tp03),
  partition tp02 values less than(maxvalue)
  (subpartition stp04 values('w') tablespace tp04,
   subpartition stp05 values('m') tablespace tp05,
   subpartition stp06 values('o') tablespace tp06)
);


insert into info4 values(1,'fishcat1',18,'w');
insert into info4 values(2,'fishcat2',30,'m');
insert into info4 values(3,'fishcat3',40,'o');
insert into info4 values(4,'fishcat4',60,'w');
insert into info4 values(5,'fishcat5',80,'m');
insert into info4 values(6,'fishcat6',150,'o');


select * from info4 partition(tp01);

select * from info4 partition(tp02);

select * from info4 subpartition(stp01);

select * from info4 subpartition(stp02);

select * from info4 subpartition(stp03);

select * from info4 subpartition(stp04);

select * from info4 subpartition(stp05);

select * from info4 subpartition(stp06);