Oracle分区表详解，实用干货

擅长Oracle数据库运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。

1.概述

---

分区：将比较大的表或者索引拆分成更小、更易于管理的片段。

每个分区对应一个 segment;

每个分区可以存储在独立的表空间（多个空间中）；

可以为分区表创建 local 或 golbal 索引。

单张表超过 2G，表中包含大量历史数据或者需要将表中数据存储在不同存储介质中时，

可以考虑使用分区表。

2.分区功能

---

1、增强性能

分区裁剪（Partition Pruning）：可以访问具体分区的数据而避免访问整张表。

智能连接（Partition-Wise Joins）：分区表和分区表关联查询，可以实现分区和分区之间的关联。

2、易于管理

提供了更小的管理单元，便于历史数据的管理。

3、提高可用性

不同分区可以存储在不同的表空间，分区之间相互独立，单个分区不可用不影响其他分区，可以对单独的分区进行备份恢复操作。

3.分区类型

---

3.1 范围分区

按照分区键值的范围，将数据存储在不同分区，

分区键一般是 date 或 number 类型，比较容易判断记录所在的分区，

每个分区的记录数可能不均衡。

create table t_range

partition by range (sal)

(

partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than (3000)

) as select * from emp where 1=2;

##查询分区

SCOTT@ORCL> select * from t_range partition (p1);

SCOTT@ORCL> select * from t_range partition (p2);

SCOTT@ORCL> select * from t_range partition (p3);

##增加分区

SCOTT@ORCL> alter table t_range add partition PMAX values less than (maxvalue);

##split分区

alter table T_RANGE split PARTITION PMAX AT(5000)

INTO (PARTITION P5,PARTITION PMAX) UPDATE GLOBAL INDEXES;

##跨分区update

SCOTT@PROD> alter table t_range enable row movement;

SCOTT@PROD> update t_range set sal =4500 where empno=7369;

SCOTT@PROD> alter table t_range disable row movement;

3.2 list分区

通过离散的键值将数据存储在不同的分区，多用于按地域进行分区

create table stu_part (

stu_year varchar2(4),

stu_no varchar2(10),

stu_memo varchar2(2000)

)

partition by list(stu_year)

(partition p_2012 values(‘2012’),

partition p_2013 values(‘2013’),

partition p_2014 values(‘2014’),

partition p_2015 values(‘2015’),

partition p_2016 values(‘2016’),

partition p_2017 values(‘2017’,‘2018’), --可以指定多个值

partition p_other values (DEFAULT)

);

INSERT INTO stu_part values(‘2017’,‘jeames’,‘学生会主席’);

INSERT INTO stu_part values(‘2021’,‘IT’,‘课代表’);

3.3 间隔分区

CREATE TABLE sales

( prod_id NUMBER(6)

, cust_id NUMBER

, time_id DATE

, channel_id CHAR(1)

, promo_id NUMBER(6)

, quantity_sold NUMBER(3)

, amount_sold NUMBER(10,2)

)

PARTITION BY RANGE (time_id)

INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’))

(

PARTITION p2006 VALUES LESS THAN (TO_DATE(‘1-1-2007’, ‘DD-MM-YYYY’)),