oracle 分区表介绍

oracle 分区表介绍

---

Oracle 分区表是一个非常强大的数据库功能，可以将一个大的表分割成多个更小、更易管理的块（分区）。这种分区结构在处理大规模数据时非常有用，因为它能改善性能、简化维护和管理，并支持高效的数据存取

---

前言

分区表（Partitioned Table）是数据库中一种特殊的表结构，它通过将数据分割成多个分区来提高查询和管理性能。每个分区都包含数据的一个子集，分区表通常用于存储大量数据，通过分区使得操作数据更为高效。分区表的优点包括：

提高查询性能：对于大数据量的表，通过查询特定分区的数据而非整个表来提高查询效率。
提高管理效率：分区表可以将数据根据某些条件（如时间、范围、列表等）分区，使得数据的备份、恢复和删除等操作更为方便。
提高并发性：分区表可以实现不同分区并行处理，提升数据库的并发能力。

分区的类型
范围分区（Range Partitioning）：按范围划分数据，例如按日期区分数据。
列表分区（List Partitioning）：按照指定的离散值划分数据，比如按地区分。
哈希分区（Hash Partitioning）：通过哈希函数将数据分配到不同的分区中，常用于负载均衡。
复合分区（Composite Partitioning）：结合多种分区方式，如先按范围分区，再按哈希分区。

我们 用soctt 用户的 emp表 和 dept 表 举例

emp表 
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','CLERK',7566,to_date('19-4-1987','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('23-5-1987','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

---

范围分区

解释：
表定义：emp_partitioned 表和原始的 EMP 表结构相同，包含了员工的基本信息字段。

分区方式：根据员工的 HIREDATE（入职日期）列进行分区，使用 范围分区（Range Partitioning）。每个分区包含一个特定年份的数据，VALUES LESS THAN 定义了每个分区的上限日期。

分区名称：我们为每个分区命名，如 p_1980、p_1981 等，代表每年的数据。

-- 创建一个基于范围分区的 EMP 表
CREATE TABLE emp_partitioned (empno      NUMBER(4),ename      VARCHAR2(10),job        VARCHAR2(9),mgr        NUMBER(4),hiredate   DATE,sal        NUMBER(7,2),comm       NUMBER(7,2),deptno     NUMBER(2)
)
PARTITION BY RANGE (hiredate) (PARTITION p_1980 VALUES LESS THAN (TO_DATE('1981-01-01', 'YYYY-MM-DD')),PARTITION p_1981 VALUES LESS THAN (TO_DATE('1982-01-01', 'YYYY-MM-DD')),PARTITION p_1982 VALUES LESS THAN (TO_DATE('1983-01-01', 'YYYY-MM-DD')),PARTITION p_1983 VALUES LESS THAN (TO_DATE('1984-01-01', 'YYYY-MM-DD')),PARTITION p_1984 VALUES LESS THAN (TO_DATE('1985-01-01', 'YYYY-MM-DD'))
);

导入数据

INSERT INTO emp_partitioned
SELECT * FROM EMP where to_char(hiredate,'YYYY-MM-DD')<'1986-01-01'

查询 emp_partitioned 的 区分p_1982

SELECT * 
FROM emp_partitioned PARTITION (p_1982) E
LEFT JOIN DEPT D  ON E.EMPNO = D.DEPTNO;

– 查询 1992 年入职的员工
查询分区表
当表被分区后，查询时 Oracle 会自动根据查询条件来裁剪不相关的分区。例如，如果查询的是 1982 年的数据，Oracle 只会访问 p_1982 分区，而不需要扫描所有的分区。

SELECT * FROM emp_partitioned
WHERE hiredate BETWEEN TO_DATE('1982-01-01', 'YYYY-MM-DD') AND TO_DATE('1982-12-31', 'YYYY-MM-DD');

添加分区

ALTER TABLE emp_partitioned ADD PARTITION p_1985 VALUES LESS THAN (TO_DATE('1986-01-01', 'YYYY-MM-DD'));

拆分分区：

ALTER TABLE emp_partitioned SPLIT PARTITION p_1981 AT (TO_DATE('1981-06-01', 'YYYY-MM-DD')) 
INTO (PARTITION p_1981_1 VALUES LESS THAN (TO_DATE('1981-06-01', 'YYYY-MM-DD')), PARTITION p_1981_2 VALUES LESS THAN (TO_DATE('1982-01-01', 'YYYY-MM-DD')));

删除分区：

ALTER TABLE emp_partitioned DROP PARTITION p_1980;

列表分区

假设我们有 EMP 表，并希望根据 DEPTNO（部门编号）对员工进行分区。使用列表分区的好处是可以根据部门编号将员工数据分布到不同的分区中，使得每个部门的数据存储在不同的分区里。

CREATE TABLE emp_partitioned_list (empno      NUMBER(4),ename      VARCHAR2(10),job        VARCHAR2(9),mgr        NUMBER(4),hiredate   DATE,sal        NUMBER(7,2),comm       NUMBER(7,2),deptno     NUMBER(2)
)
PARTITION BY LIST (deptno) (PARTITION p_sales VALUES (10),  -- 部门编号为 10 的员工PARTITION p_marketing VALUES (20),  -- 部门编号为 20 的员工PARTITION p_research VALUES (30),  -- 部门编号为 30 的员工PARTITION p_accounting VALUES (40)  -- 部门编号为 40 的员工
);

导入数据

INSERT INTO emp_partitioned_list
SELECT * FROM EMP where deptno in (10,20,30,40)

添加新的分区：
如果我们有一个新的部门编号 50，可以添加新的分区：

ALTER TABLE emp_partitioned_list ADD PARTITION p_new_dept VALUES (50);

哈希分区

假设我们要对 EMP 表进行哈希分区，使用员工编号 EMPNO 作为分区键。哈希分区会使用哈希函数将数据均匀地分配到不同的分区中，适用于没有明显规律的列。

– 创建一个哈希分区的 EMP 表

分区名称：通过 PARTITION p_part1、PARTITION p_part2、PARTITION p_part3 和 PARTITION p_part4，你可以自定义每个分区的名称。
分区键：该表仍然使用 empno 列作为分区键，Oracle 将会基于 empno 字段的哈希值将数据分配到这些分区中。

CREATE TABLE emp_partitioned_hash (empno      NUMBER(4),ename      VARCHAR2(10),job        VARCHAR2(9),mgr        NUMBER(4),hiredate   DATE,sal        NUMBER(7,2),comm       NUMBER(7,2),deptno     NUMBER(2)
)
PARTITION BY HASH (empno)  -- 使用 empno 作为分区键PARTITIONS 4;  -- 将表分为 4 个分区
-----
PARTITION BY HASH (empno)  -- 使用 empno 作为分区键(PARTITION p_part1,  -- 自定义分区名称 p_part1PARTITION p_part2,  -- 自定义分区名称 p_part2PARTITION p_part3,  -- 自定义分区名称 p_part3PARTITION p_part4   -- 自定义分区名称 p_part4);

导入数据

insert into emp_partitioned_hash
select * from emp

复合分区

复合分区结合了多种分区方法，可以在范围分区的基础上进一步使用哈希分区。这样可以灵活地控制数据的分区。

假设我们有一个 EMP 表，我们按员工的入职日期 (HIREDATE) 进行范围分区，然后在每个年份的分区内按部门编号 (DEPTNO) 使用哈希分区。以下是如何创建这样的表的示例。。

-- 创建复合分区的 EMP 表
CREATE TABLE EMP_Partition_Range_Hash (empno      NUMBER(4),ename      VARCHAR2(10),job        VARCHAR2(9),mgr        NUMBER(4),hiredate   DATE,sal        NUMBER(7,2),comm       NUMBER(7,2),deptno     NUMBER(2)
)
PARTITION BY RANGE (hiredate)  -- 首先按 HIREDATE 进行范围分区
SUBPARTITION BY HASH (deptno)  -- 在每个范围分区内按 DEPTNO 进行哈希分区
(PARTITION p_1980 VALUES LESS THAN (TO_DATE('1981-01-01', 'YYYY-MM-DD'))  -- 1980 年的数据(SUBPARTITION p_1980_1,  -- 子分区1SUBPARTITION p_1980_2   -- 子分区2),PARTITION p_1981 VALUES LESS THAN (TO_DATE('1982-01-01', 'YYYY-MM-DD'))  -- 1981 年的数据(SUBPARTITION p_1981_1,  -- 子分区1SUBPARTITION p_1981_2   -- 子分区2),PARTITION p_1982 VALUES LESS THAN (TO_DATE('1983-01-01', 'YYYY-MM-DD'))  -- 1982 年的数据(SUBPARTITION p_1982_1,  -- 子分区1SUBPARTITION p_1982_2   -- 子分区2)
);

以下是一个 复合分区（Composite Partitioning）示例，其中结合了 范围分区（Range Partitioning）和 列表分区（List Partitioning）。我们将创建一个 EMP 表，首先按员工的 HIREDATE（入职日期）进行 范围分区，然后在每个范围分区内按 DEPTNO（部门编号）进行 列表分区。

-- 创建一个复合分区的 EMP 表
CREATE TABLE EMP_Partition_Range_List (empno      NUMBER(4),ename      VARCHAR2(10),job        VARCHAR2(9),mgr        NUMBER(4),hiredate   DATE,sal        NUMBER(7,2),comm       NUMBER(7,2),deptno     NUMBER(2)
)
PARTITION BY RANGE (hiredate)  -- 首先按 HIREDATE 进行范围分区
SUBPARTITION BY LIST (deptno)  -- 在每个范围分区内按 DEPTNO 进行列表分区
(PARTITION p_1980 VALUES LESS THAN (TO_DATE('1981-01-01', 'YYYY-MM-DD'))  -- 1980 年的数据(SUBPARTITION p_1980_1 VALUES (10),  -- 部门 10 的子分区SUBPARTITION p_1980_2 VALUES (20),  -- 部门 20 的子分区SUBPARTITION p_1980_3 VALUES (30)   -- 部门 30 的子分区),PARTITION p_1981 VALUES LESS THAN (TO_DATE('1982-01-01', 'YYYY-MM-DD'))  -- 1981 年的数据(SUBPARTITION p_1981_1 VALUES (10),  -- 部门 10 的子分区SUBPARTITION p_1981_2 VALUES (20),  -- 部门 20 的子分区SUBPARTITION p_1981_3 VALUES (30)   -- 部门 30 的子分区),PARTITION p_1982 VALUES LESS THAN (TO_DATE('1983-01-01', 'YYYY-MM-DD'))  -- 1982 年的数据(SUBPARTITION p_1982_1 VALUES (10),  -- 部门 10 的子分区SUBPARTITION p_1982_2 VALUES (20),  -- 部门 20 的子分区SUBPARTITION p_1982_3 VALUES (30)   -- 部门 30 的子分区)
);

导入数据

insert into EMP_Partition_Range_hash
select * from emp where deptno in (10,20,30)  and to_char(hiredate,'yyyy-mm-dd')<'1984-01-01'insert into EMP_Partition_Range_List
select * from emp where deptno in (10,20,30)  and to_char(hiredate,'yyyy-mm-dd')<'1984-01-01'

查询数据

select * from EMP_Partition_Range_hash PARTITION(p_1981)
select * from EMP_Partition_Range_hash SUBPARTITION (p_1980_1)select * from EMP_Partition_Range_List PARTITION(p_1981)
select * from EMP_Partition_Range_List SUBPARTITION (p_1981_1)

添加分区 (Add Partition)
假设您希望为现有的复合分区表添加一个新的范围分区。您可以使用 ALTER TABLE 语句来实现这一操作。这里我们为表 EMP_Partition_Range_List 添加一个新的范围分区 p_1983，并在该分区内使用部门编号（DEPTNO）进行子分区。

ALTER TABLE EMP_Partition_Range_List
ADD PARTITION p_1983 VALUES LESS THAN (TO_DATE('1983-01-01', 'YYYY-MM-DD'))
(SUBPARTITION p_1983_1 VALUES (10),SUBPARTITION p_1983_2 VALUES (20),SUBPARTITION p_1983_3 VALUES (30)
);

删除分区 (Drop Partition)
如果某个分区已经不再需要或已经包含过期数据，可以删除该分区。假设您希望删除 p_1980 分区及其子分区，可以使用如下 SQL 语句：

ALTER TABLE EMP_Partition_Range_List
DROP PARTITION p_1980;

拆分分区 (Split Partition)
当某个分区中的数据量过大时，您可以拆分分区以分散负载。假设您希望拆分 p_1981 分区，将其拆分为两个新的分区，您可以使用以下语句

ALTER TABLE EMP_Partition_Range_List
SPLIT PARTITION p_1981 AT (TO_DATE('1981-06-30', 'YYYY-MM-DD'))
INTO (PARTITION p_1981_1 VALUES LESS THAN (TO_DATE('1981-06-30', 'YYYY-MM-DD')),PARTITION p_1981_2 VALUES LESS THAN (TO_DATE('1982-01-01', 'YYYY-MM-DD'))
);

合并分区 (Merge Partition)
当多个分区的数据量较小且可以合并时，可以使用 MERGE PARTITION 语句。假设您希望将 p_1980_1 和 p_1980_2 两个子分区合并为一个新的子分区，可以使用如下语句：

ALTER TABLE EMP_Partition_Range_List
MERGE PARTITIONS p_1980_1, p_1980_2 INTO PARTITION p_1980;

交换分区 (Exchange Partition)
如果您需要将一个分区的数据与一个表的数据交换，可以使用 EXCHANGE PARTITION 语句。假设您有一个临时表 new_data_table，并且希望将 p_1981_1 的数据与 new_data_table 交换，可以使用以下语句：

ALTER TABLE EMP_Partition_Range_List
EXCHANGE PARTITION p_1981_1 WITH TABLE new_data_table;

修改分区存储属性
如果您需要修改某个分区的存储属性（例如修改分区的存储参数），可以使用 ALTER TABLE 来修改。例如，如果您想要增加分区的 PCTFREE 参数，可以使用如下语句：

ALTER TABLE EMP_Partition_Range_List
MODIFY PARTITION p_1981 STORAGE (PCTFREE 20);

查询分区

select * from user_tab_partitions 
select * from DBA_tab_partitions 
select * from ALL_tab_partitions 
----查看所有分区信息：
----这里的表名是您需要查询的表
SELECT table_name, partition_name, partition_position, tablespace_name, high_value
FROM DBA_TAB_PARTITIONS
WHERE table_name = 'EMP_PARTITION_RANGE_LIST';  
----查看每个分区的子分区信息
SELECT table_name, partition_name, subpartition_name, subpartition_position
FROM DBA_TAB_SUBPARTITIONS
WHERE table_name = 'EMP_PARTITION_RANGE_LIST';
----查看分区的列和分区键：
SELECT column_name, partitioned
FROM DBA_TAB_COLUMNS
WHERE table_name = 'EMP_PARTITION_RANGE_LIST';
----查看某个分区的存储属性：
SELECT table_name, partition_name, pct_free, tablespace_name
FROM DBA_TAB_PARTITIONS
WHERE table_name = 'EMP_PARTITION_RANGE_LIST';
----查看表空间中的所有分区
SELECT table_name, partition_name, tablespace_name
FROM DBA_TAB_PARTITIONS
WHERE tablespace_name = 'USERS';  -- 这里的 'USERS' 是指定的表空间名

分区表的使用场景

时间序列数据：

对于大规模的时间序列数据，分区表是非常适合的。例如，日志数据、交易数据等。通常，按年份、季度、月份等时间粒度进行分区，可以极大地提高查询效率，并方便旧数据的归档和删除。
示例：

银行交易记录按月份分区，查询某个月的交易记录时，数据库仅需要扫描该月份的分区。

大规模数据仓库：

在数据仓库系统中，经常需要处理数TB甚至PB级的数据，分区表可以显著提高查询性能，并且优化存储管理。通过分区，可以根据不同的维度（如时间、地区、产品类型等）进行数据分区。
示例：

数据仓库的销售记录按地区和时间进行复合分区（先按时间分区，再按地区分区），提高查询性能和数据处理效率。
数据归档和历史数据管理：

处理大量历史数据时，分区表可以通过分区裁剪和快速删除，简化数据归档和清理。例如，按年份分区的表可以在每年结束时快速归档过去一年的数据。
示例：

大型企业的员工记录表按年份分区，每年创建一个新的分区，过期的员工记录（例如离职超过5年的员工）可以快速删除。
高并发环境：

在处理大量并发查询的环境中，分区表能够将负载分布到不同的分区上，提高并发查询的响应速度。例如，对于一个在线零售商，按地区对订单表进行分区，可以提高不同地区用户查询的并发性能。
示例：

电商平台的订单表按地区分区，不同地区的查询可以在独立的分区上并行执行，从而提高查询性能。
大规模日志数据存储：

日志数据通常按时间顺序增长，分区表特别适合存储和管理这类数据。按月或按天分区的日志表能极大提高查询速度，并便于清理过时的数据。
示例：

Web 服务器的访问日志按天分区，每天生成一个新的分区，查询某天的访问记录时，只需扫描相关的分区。
跨分区查询优化：

如果需要在多个分区之间执行查询，使用分区表可以显著优化跨分区的查询操作。通过合理的分区策略（例如按客户地域、订单类型等分区），可以使查询仅集中在少量相关的分区上，而不需要遍历整个表。
示例：

客户订单表按地区分区，查询某个地区所有订单时，仅需访问该地区对应的分区。