1 概述

Oracle表分区是将一个大型表分割成更小、更易于管理的部分的技术。分区后的表被称为分区表，其中每个分区都可以独立地进行维护、管理和查询。表分区可基于表中的一列或多列，称为分区键，分区键的值确定了每行数据属于哪个分区。

使用分区具有以下优点：

（1）改善查询性能：由于表分区将数据分割成更小、更可管理的部分，对分区对象的查询可以仅搜索特定分区，提高检索速度。如在范围分区的情况下，可以更快地查询特定时间段的数据。
-（2）维护方便：分而治之，每个分区都可以独立地进行维护和管理，更容易地维。如表的某个分区出现故障，需要修复数据，只修复该分区即可；又如在范围分区的情况下，可以更容易地删除或归档旧数据。
（3）可用性：实际各分区的数据是独立存放，如果表的某个分区出现故障，表在其他分区的数据仍然可用；
（4）均衡I/O：可把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；并且可以更快地加载数据，因为可以并行加载多个分区。如在哈希分区的情况下，可以并行加载多个分区，从而大大提高了数据加载的速度。

1.1 思维导图

2 分类

2.1 传统表分区

2.1.1 范围分区 range

范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。

情况1：数值范围分区

create table pt_range_test1(
  pid   number(10),
  pname varchar2(30)
) partition by range(pid)(
  partition p1 values less than(1000) tablespace tetstbs1,
  partition p2 values less than(2000) tablespace tetstbs2,
  partition p3 values less than(maxvalue) tablespace tetstbs3
) enable row movement;

插入数据：

insert into pt_range_test1 (pid, pname) values (1, '瑶瑶');
insert into pt_range_test1 (pid, pname) values (1500, '倩倩');
insert into pt_range_test1 (pid, pname) values (null, '优优');
commit;

查询数据：

select * from user_tab_partitions t;
select 'P1' 分区名, t.* from pt_range_test1 partition (p1) t union all
select 'P2' 分区名, t.* from pt_range_test1 partition (p2) t union all
select 'P3' 分区名, t.* from pt_range_test1 partition (p3) t

情况2：时间范围分区（同理）

create table pt_range_test2(
  pid         number(10),
  pname       varchar2(30),
  create_date date
) partition by range(create_date)(
  partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD')) tablespace tetstbs1,
  partition p2 values less than(to_date('2021-01-01', 'YYYY-MM-DD')) tablespace tetstbs2,
  partition p3 values less than(maxvalue) tablespace tetstbs3
) enable row movement;

可以对分区表进行跟新或删除，默认情况下，oracle的分区表对于分区字段是不允许进行update操作的，如果有对分区字段行进update，就会报ORA-14402错误: 更新分区关键字列将导致分区的更改。但是可以通过打开表的row movement属性来允许对分区字段的update操作，但是这样会导致无效对象的产生，所以不推荐使用。可通过删除旧数据，插入新数据来解决。

还可以根据需求，使用两个字段的范围分布来分区，如partition by range ( transaction_id ，transaction_date)， 分区条件中的值也做相应的改变。

2.1.2 列表分区 list

create table pt_list_test(
  pid   number(10),
  pname varchar2(30),
  sex   varchar2(10)
) partition by list(sex)(
  partition p1 values ('MAN', '男') tablespace tetstbs1,
  partition p2 values ('WOMAN', '女') tablespace tetstbs2,
  partition p3 values (default) tablespace tetstbs3
) enable row movement;

插入数据：

insert into pt_list_test (pid, pname, sex) values (1, '瑶瑶', '男');
insert into pt_list_test (pid, pname, sex) values (2, '倩倩', 'WOMAN');
insert into pt_list_test (pid, pname, sex) values (3, '优优', 'GOD');
commit;

查询数据：

select 'P1' 分区名, t.* from pt_list_test partition (p1) t union all
select 'P2' 分区名, t.* from pt_list_test partition (p2) t union all
select 'P3' 分区名, t.* from pt_list_test partition (p3) t

2.1.3 哈希分区 hash

散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。如将物料交易表的数据根据交易ID散列地存放在指定的三个表空间中

create table pt_hash_test(
  pid   number(10),
  pname varchar2(30)
) partition by hash(pid)(
  partition p1 tablespace tetstbs1,
  partition p2 tablespace tetstbs2,
  partition p3 tablespace tetstbs3,
  partition p4 tablespace tetstbs4,
);

简写：

create table pt_hash_test2(
  pid   number(10),
  pname varchar2(30)
) partition by hash(pid) 
  partitions 4 store in (tetstbs1, tetstbs2, tetstbs3, tetstbs4);

2.1.4 复合分区 range + list or hash

有时需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内 再使用散列分区的一种分区方法，如将物料交易的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中：

情况1：range + list

create table pt_range_list_test(
   pid         number(10),
   pname       varchar2(30),
   sex         varchar2(10),
   create_date date
) partition by range(create_date) 
  subpartition by list(sex)(
    partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD')) tablespace tetstbs1(
      subpartition sub1p1 values('MAN') tablespace tetstbs1,
      subpartition sub2p1 values('WOMAN') tablespace tetstbs1,
      subpartition sub3p1 values(default) tablespace tetstbs1
    ),
    partition p2 values less than(to_date('2021-01-01', 'YYYY-MM-DD')) tablespace tetstbs2(
      subpartition sub1p2 values('MAN') tablespace tetstbs2,
      subpartition sub2p2 values('WOMAN') tablespace tetstbs2,
      subpartition sub3p2 values(default) tablespace tetstbs2
    ),
    partition p3 values less than(maxvalue) tablespace tetstbs3(
      subpartition sub1p3 values('MAN') tablespace tetstbs3,
      subpartition sub2p3 values('WOMAN') tablespace tetstbs3,
      subpartition sub3p3 values(default) tablespace tetstbs3
    )
  ) enable row movement;

情况2：range + hash

create table pt_range_hash_test(
pid number(10),
pname varchar2(30),
sex varchar2(10),
create_date date
) partition by range(create_date)
subpartition by hash(pid) subpartitions 4 store in (tetstbs1, tetstbs2, tetstbs3, tetstbs4)(
partition p1 values less than(to_date(‘2020-01-01’, ‘YYYY-MM-DD’)) tablespace tetstbs1,
partition p2 values less than(to_date(‘2021-01-01’, ‘YYYY-MM-DD’)) tablespace tetstbs2,
partition p3 values less than(to_date(‘2022-01-01’, ‘YYYY-MM-DD’)) tablespace tetstbs3,
partition p4 values less than(maxvalue) tablespace tetstbs4
) enable row movement;

2.2 11g 新特性分区

2.1.1 引用分区 reference

外键列必须 not null
– 父表

create table pt_reference_father_test(
   pid         number(10),
   pname       varchar2(30),
   create_date date,
   constraint pk_ptrft_pid primary key(pid)
) partition by range(create_date)(
    partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD')) tablespace tetstbs1,
    partition p2 values less than(to_date('2021-01-01', 'YYYY-MM-DD')) tablespace tetstbs2,
    partition p3 values less than(maxvalue) tablespace tetstbs3
  ) enable row movement;

– 子表

create table pt_reference_son_test(
   pid     number(10) not null, -- 必须 not null，否则报错
   item_id number(10),
   constraint pk_ptrst_item_id primary key(item_id),
   constraint fk_ptrst_pid foreign key(pid)
   references pt_reference_father_test(pid)       
) partition by reference(fk_ptrst_pid)
  enable row movement;

2.1.2 间隔分区 interval

必须有个初始分区，且无法删除（除非直接删除表）
– 初始时间范围分区 2020-01-01
– 之后数据每间隔 1 年，新建一个分区

create table pt_interval_test(
  pid         number(10),
  pname       varchar2(30),
  create_date date
) partition by range(create_date) 
  interval(numtoyminterval(1, 'YEAR'))(
     partition p1 values less than(to_date('2020-01-01', 'YYYY-MM-DD')) tablespace tetstbs1
  );

分别插入数据，观察变化：

insert into pt_interval_test(pid, pname, create_date) values(1, '瑶瑶', to_date('2019-01-01', 'YYYY-MM-DD'));
insert into pt_interval_test(pid, pname, create_date) values(2, '倩倩', to_date('2020-01-01', 'YYYY-MM-DD'));
select * from user_tab_partitions t where t.table_name = upper('pt_interval_test');

2.1.3 虚拟列分区 virtual

将分区建立在某个虚拟列上（函数或表达式 的计算结果上）

create table pt_virtual_test(
  pid         number(10),
  pname       varchar2(30),
  create_date date,
  create_quarterly as (to_char(create_date,'D')) virtual
) partition by list(create_quarterly)(
    partition p1 values(1) tablespace tetstbs1,
    partition p2 values(2) tablespace tetstbs2,
    partition p3 values(3) tablespace tetstbs3,
    partition p4 values(4) tablespace tetstbs4,
    partition p5 values(default) tablespace tetstbs4
  );

2.1.4 系统分区 system

不能指定分区列

create table pt_system_test(
  pid   number(10),
  pname varchar2(30)
) partition by system(
    partition p1 tablespace tetstbs1,
    partition p2 tablespace tetstbs2,
    partition p3 tablespace tetstbs3
  );

3 管理

3.1 表分区

1. 查询:
   (1) select * from user_tab_partitions t;
2. 添加:
   (1) alter table add partition values less than(to_date(‘2020-02-02’, ‘YYYY-MM-DD’));
   (2) alter table add partition values less than(1000);
3. 删除: (请注意：无法删除分区表唯一的分区，除非删除表)
   (1) alter table drop partition ;
   (2) alter table drop subpartition ;
4. 截断分区（’清空某个分区的数据’）
   (1) alter table truncate partition ;
   (2) alter table truncate subpartition ;
5. 拆分分区（’拆分后，原来分区不再存在’）
   (1) alter table sblit partition at(to_date(‘2020-01-01’, ‘YYYY-MM-DD’)) into (partition p1, partition p2);
6. 合并分区
   (1) alter table merge partitions , into partition ;
7. 重命名分区
   (1) alter table rename partition to

3.2 表空间

1. 查询
   (1) select * from user_tablespaces t;
2. 创建 – 创建表空间时，可选项有很多，此处仅列出必选项
   create tablespace “tbs”
   datafile ‘D:\oracle\tbs_01.dbf’
   size 10m;
3. 删除
   (1) 仅删除表空间：drop tablespace tbs;
   (2) 删除表空间和数据文件：drop tablespace tbs including contents and datafiles;