oracle 分区表

分区表的概念,简单来说就是:原来一个用户下表的数据是放在一个表空间里,使用了分区表后,表的数据是放在多个表空间中。

PS:表空间是包括一个用户下所有文件的逻辑概念。

如果一个数据库中,某几个表(特别是历史表)占了数据库50%以上的空间,或达到几百G的磁盘容量。这时就要考虑使用分区表了。

Oracle表分区分为四种:范围分区(Range分区),散列分区(Hash分区),列表分区(List分区)和复合分区(范围-散列分区range-hash,范围-列表分区range-list)

分区的好处,主要用到以下两点:
改善查询性能
维护备份数据方便

一、范围分区
范围分区就是根据表中某一字段的值的范围来分区,特别适用于时间日期、ID号来分区

1、建立测试用户

create user wangyi identified by wangyi;
grant dba to wangyi;
conn wangyi/wangyi;

2、建立测试表和数据
//策略是按月创建分区,保留一年数据,年末备份前年数据。

例子:

create table range_table (
inst_date date,
produce_id number,
amt varchar(12)
)
partition by range(inst_date)
(
partition p1 values less than(to_date('01/02/2016','DD/MM/YYYY')),
partition p2 values less than(to_date('01/03/2016','DD/MM/YYYY')),
partition p3 values less than(to_date('01/04/2016','DD/MM/YYYY')),
partition p4 values less than(to_date('01/05/2016','DD/MM/YYYY')),
partition p5 values less than(to_date('01/06/2016','DD/MM/YYYY'))
);


插入测试数据:

insert into range_table values(to_date('01/02/2016','DD/MM/YYYY'),1,'0.1');
insert into range_table values(to_date('01/03/2016','DD/MM/YYYY'),2,'0.2');
insert into range_table values(to_date('01/04/2016','DD/MM/YYYY'),3,'0.3');

PS:分区表不一定要放到不同的表空间中,如要放入不同的表空间,需先建立表空间文件,然后在partition语句后面加入tablespace space_nameXX。

3、查询分区情况
1)

select * from dba_part_tables where owner = 'WANGYI';

2)

select count(*) from range_table;

  COUNT(*)
----------
	 3


3)

select count(*) from range_table partition (p2);

  COUNT(*)
----------
	 1


可以看到3条记录,落到了3个分区中。

4、11g可以用interval函数自动分区

create table range_table (
inst_date date,
produce_id number,
amt varchar(12)
)
partition by range(inst_date)
interval(numtoyminterval(1, 'month'))
(
partition p1 values less than(to_date('2016-01-01','YYYY-MM-DD'))
);

二、散列分区
散列分区是根据字段的hash值进行均匀分布,尽可能的实现各分区所散列的数据相等。

例子:

create table hash_table (
inst_date date,
produce_id number,
amt varchar(12)
)
partition by hash(inst_date)
(
partition p1,
partition p2,
partition p3
);

insert into hash_table values(to_date('01/02/2016','DD/MM/YYYY'),1,'0.1');
insert into hash_table values(to_date('01/03/2016','DD/MM/YYYY'),2,'0.2');
insert into hash_table values(to_date('01/04/2016','DD/MM/YYYY'),3,'0.3');

2、查询分区情况
1)

select count(*) from hash_table;

  COUNT(*)
----------
	 3


2)

select count(*) from hash_table partition (p1);

  COUNT(*)
----------
	 1


3)

select count(*) from hash_table partition (p2);

  COUNT(*)
----------
	 2


记录具体落到哪个分区是由oracle计算hash值后决定的。

三、列表分区
列表分区明确指定了根据某字段的某个具体值进行分区,而不是像范围分区那样根据字段的值范围来划分的。

列表分区不支持多列,但是范围分区和哈希分区支持多列。

例子:

create table list_table (
inst_date date,
produce_id number,
amt varchar(12)
)
partition by list(produce_id)
(
partition p1 values(1),
partition p2 values(2),
partition p3 values(3)
);

insert into list_table values(to_date('01/02/2016','DD/MM/YYYY'),1,'0.1');
insert into list_table values(to_date('01/03/2016','DD/MM/YYYY'),2,'0.2');
insert into list_table values(to_date('01/04/2016','DD/MM/YYYY'),3,'0.3');

3、查询分区情况
1)

select count(*) from list_table partition (p1);

  COUNT(*)
----------
	 1


2)

select count(*) from list_table partition (p2);

  COUNT(*)
----------
	 1


3)

select count(*) from list_table partition (p3);

  COUNT(*)
----------
	 1


四、复合分区(范围-散列分区,范围-列表分区)
复合分区就是可以在分区里再建子分区。

比较复杂,见参考资料第一个链接。。。

五、对分区的维护

1、清空表分区数据

alter table 表名 truncate partition 分区名;

2、直接删除分区

alter table 表名 drop partition 分区名;

参考资料:
http://love-flying-snow.iteye.com/blog/573303
http://hougbin.iteye.com/blog/1470505
http://blog.yemou.net/article/query/info/tytfjhfascvhzxcytp51
http://www.cnblogs.com/leiOOlei/archive/2012/06/08/2541306.html