oracle 分区表方法详解介绍

作者：袖梨 2022-06-29

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

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

四、复合分区（范围-散列分区，范围-列表分区）
复合分区就是可以在分区里再建子分区。

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

五、对分区的维护