oracle 分区条件加函数,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

四、复合分区(范围-散列分区，范围-列表分区)

复合分区就是可以在分区里再建子分区。

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

五、对分区的维护