oracle分区表

何时该分区

1. 大于2GB的表
2. 含有1000万条记录以上的表，表中含有的数据越多，SQL操作的执行速度就会越慢。
3. 将会含有大量数据的表。
4. 强行拆分后可利于并行操作的表。
5. 含有需要定期归档日志或删除部分的表

分区表的优点

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

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

3. 可以将分区映射到不同的物理磁盘上，来分散I/O

4. 提高可管理性，可用性

分区表策略分类

范围 --> 根据日期,数值或字符创建分区
列表 --> 根据列表值创建分区
散列 --> 在没有明显分区键的情况下，以均分方式创建分区
组合 --> 组合使用多种分区方式
间隔 --> 当新分区键值超出现存最大范围时，通过自动分配新分区扩展范围分区
引用 --> 根据父表列为子表创建分区
虚拟列分区 --> 在虚拟列上创建分区
系统 --> 根据插入数据的应用程序创建分区

常用的分区类型为：范围、列表和散列

 查询数据量排行前十的表

一条可靠的原则就是大于2GB的表就可以应用分区功能。运行下面的查询命令可以显示数据库中占用空间的排名情况：
select * from (
select owner,segment_name,segment_type,partition_name,sum(bytes)/1024/1024 meg_tot
from dba_segments group by owner,segment_name,segment_type,partition_name
order by sum(extents) desc)
where rownum <=10 

 范围分区

-- 创建一个普通表的语句
create table person1 (id int primary key, name varchar2(20), birth date);
-- 数据将会在同一个表空间同一个段内
insert into person1 values (1, 'sss', sysdate);
 
-- 创建一个分区表
-- 这里是按照生日进行范围分区
-- 语句的基本格式就是在普通建表的语句上，增加 partition by 语句块
create table person2 (name varchar2(20), birth date)
partition by range (birth)
(
  partition p1 values less than (to_date('19950101','yyyymmdd')),  -- 'values less than'
  partition p2 values less than (to_date('20000101','yyyymmdd')),
  partition p3 values less than (maxvalue)                         -- 默认分区
);
-- 插入，数据会根据分区的情况进入不同的分区内
insert into person2 values ('张三', to_date('19940707'));
insert into person2 values ('李四', to_date('19980707'));
insert into person2 values ('王五', to_date('20040707'));
-- 查询表中所有数据
select * from person2;
-- 查询特定分区上数据
select * from person2 partition (p3);
 
 
-- 可以为不同的分区指定不同的表空间
-- 没有指定表空间的分区，使用用户的默认表空间
-- 所以，一个表内的数据可以存在于不同表空间里，也就是可以存放在不同数据文件中，不同磁盘上
-- 因此，分区表能增强数据的安全性
create table person3 (name varchar2(20), birth date)
partition by range (birth)
(
  partition p1 values less than (to_date('19950101','yyyymmdd')) tablespace system,
  partition p2 values less than (to_date('20000101','yyyymmdd')) tablespace sysaux,
  partition p3 values less than (maxvalue) tablespace users
);
 
 
-- 可以在其他类型上进行范围分区
-- 也可以在多个字段上进行范围分区
create table person4 (name varchar2(20), birth date, score number)
partition by range (birth, score)
(
  partition p1 values less than (to_date('19900101','yyyymmdd'), 60),
  partition p2 values less than (to_date('19900101','yyyymmdd'), 90),
  partition p3 values less than (to_date('19990101','yyyymmdd'), 60),
  partition p4 values less than (to_date('19990101','yyyymmdd'), 90),
  partition p5 values less than (maxvalue, maxvalue)
);

 列表分区

-- 如果是生日的这样的字段，数据是连续的，应该使用分为分区
create table person (name varchar2(20), birth date)
partition by range(birth)
(
  partition p1 values less than (to_date('19900101', 'yyyymmdd')) tablespace users,
  partition p2 values less than (maxvalue)
);
insert into person values ('aaa', to_date('19871212', 'yyyymmdd'));
select * from person partition (p1);
 
 
/* 
where birth between 1987 and 1990 
where sex in ('男', '女')
*/
 
-- 但是像性别、民族等字段，更适合使用的是列表分区
-- 下面一个例子，使用性别作为分区字段，男的一个区，女的一个区
create table person2 (name varchar2(20), sex varchar(10))
partition by list (sex)
(
    partition p1 values ('男'),
    partition p2 values ('女')
);
insert into person2 values ('aaa', '男');
insert into person2 values ('bbb', '女');
insert into person2 values ('ccc', '未知');  -- 报错
select * from person2 partition (p2);
 
-- 默认分区的写法
create table person3 (name varchar2(20), sex varchar(10))
partition by list (sex)
(
    partition p1 values ('男'),
    partition p2 values ('女'),
    partition p3 values (default)
);
insert into person3 values ('ccc', '未知');
select * from person3 partition (p3);
 
 
-- 可以为每个分区指定表空间
create table person3 (name varchar2(20), sex varchar(10))
partition by list (sex)
(
    partition p1 values ('男') tablespace users,
    partition p2 values ('女') tablespace system,
    partition p3 values (default)
);

 哈希分区

-- 哈希分区
-- 主要用在一些比较离散，不好分类的数据上，比如产品名字
-- 让 oracle 使用哈希算法自动计算数据的分区
 
-- 创建语句，非常简单
create table person4 (name varchar2(20), sex varchar2(10))
partition by hash (name)
(
  partition p1,
  partition p2 tablespace users
);
insert into person4 values ('aaa', '男');
insert into person4 values ('收款', '男');
select * from person4 partition (p1);
 
-- 上面的语句可以进一步简化为：
create table person5 (name varchar2(20), sex varchar2(10))
partition by hash (name)
partitions 5;
 
-- 为每个分区指定表空间
create table person6 (name varchar2(20), sex varchar2(10))
partition by hash (name)
partitions 3 store in (users, system, sysaux);

 范围与哈希组合

-- 先按照生日，将数据分为三个区
-- 然后在每个分区内，又按照哈希算法分成了三个区
-- 这样就保证了每个分区内的数据尽量的少，而且分区进行平衡
create table person7 (name varchar2(20), birth date)
partition by range (birth)
subpartition by hash (name) subpartitions 3
(
  partition p1 values less than (to_date('19900101', 'yyyymmdd')),
  partition p2 values less than (to_date('20000101', 'yyyymmdd')),
  partition p3 values less than (maxvalue)
);

 非分区表转分区表

 12c以前非分区表需要转换为分区, 如果不停业务的话可以使用在线重定义,只有在表进行切换的时候会有短暂的锁表。 12c 中alter table online clause 实现了表上现有的索引有效,又不停业务。

-- oracle 12c
create table person7 (name varchar2(20), birth date);
--将非分区表转换为分区表，使用哈希
alter table person7 modify
partition by hash (name)
(
  partition ph1,
  partition ph2
) online
update indexes;

操作分区

-- 添加分区
alter table person add partition p9 values less than (MAXVALUE);
alter table person add partition p9 values (1, 2);   -- 针对 list 分区
alter table person add partition;                    -- 针对 hash 分区
 
-- 删除分区
alter table person drop partition p3;
 
-- 删除分区内数据
alter table person truncate partition p3;
 
-- 合并相邻分区
alter table person merge partitions p2, p3 into partition p8;
 
-- 拆分分区
alter table person split partition p2 at (3000) into (partition p3, partition p14);     -- 范围分区的拆分
alter table person split partition p2 values (1,2) into (partition p3, partition p4);   -- 列表分区的拆分
alter table person split partition p2 into (partition p3 values (1, 2), partition p4 values (3), partition p5);   -- 列表分区的拆分
 
-- 重命名分区
alter table person rename partition p2 to p12;