数据库教程FGMT13‑Oracle性能优化之数据仓库与分区表

## 前言

在Oracle数据仓库、海量业务表场景当中，单表数据量达到千万、亿行级别之后，普通堆表会出现查询慢、DML维护成本高、归档清理数据耗时久等一系列问题。分区表是OracleVLDB超大型数据库的核心能力，通过物理上将一张逻辑大表拆分为多个独立分区段，实现分区裁剪、分区级快速维护、并行DML与查询，极大优化海量数据场景查询性能与运维效率。风哥教程本文围绕分区表核心原理、各类分区类型、本地与全局分区索引、分区全套运维管理、普通表转分区表多种实现方案、生产迁移实战案例展开完整讲解。风哥 itpux‑com

本套风哥教程面向DBA、数据仓库开发、运维工程师，全部实验标准化环境配置：主机名称**fgedu‑net‑cn**，硬件规格64G物理内存、8颗CPU；数据库实例名`fgedudb`，数据库名`fgedudb`，测试业务用户名`fgedu`，文件根目录统一为`/fgedudb`，整套实验基于Oracle19c企业版完成。风哥教程本文分为前言大纲介绍、核心理论知识、实战操作演练、总结四大模块；实战章节包含大量可直接复制运行SQL脚本，读者可以在测试环境完整复现全部实验现象，掌握分区表设计、创建、索引管理、分区维护、表迁移整套生产运维手段。网上搜索风哥教程可以学习全套数据库教程

### 内容大纲

1. Oracle数据仓库与分区表基础概念，分区表核心价值与适用场景

2. Oracle各类分区类型原理：范围、列表、哈希、复合、Interval间隔、虚拟列、系统分区、参考分区

3. 本地分区索引、全局分区索引原理，二者差异、适用场景、失效风险

4. 分区表各类管理操作：增加、删除、拆分、收缩、合并、修改、截断、移动、重命名

5. 分区索引管理：增加、删除、编译、重命名、拆分、重建索引分区

6. 分区相关数据字典视图，分区表与分区索引统计信息收集管理

7. 普通表转为分区表五大实现方案，各自优缺点与适用场景

8. 分区表迁移生产实战案例，dbms_redefinition在线重定义完整实操

9. 分区裁剪、分区级并行访问原理，数据仓库场景最佳实践

10. 分区表生产风险点、设计规范、故障排查工作流程

## 一、核心理论知识

本章节为本套风哥教程理论基础，充分理解分区底层原理，才能够合理设计分区键，规避全局索引失效、分区裁剪失效、分区维护锁等待等生产故障。风哥教程 113257174

### 1.1 分区表基础概念与核心价值

分区表在逻辑上是一张完整表，物理层面拆分为多个独立的分区segment段，每一个分区可以独立存储在不同表空间。对应用户SQL仍然访问逻辑表，Oracle内部自动路由访问对应分区。

分区表带来四大核心收益：

1. **分区裁剪（Partition Pruning）**：SQL where条件携带分区键，优化器自动过滤不需要访问的分区，只扫描少量分区，大幅降低IO开销，提升查询性能。

2. **分区级运维**：可以直接对分区执行drop、truncate、exchange，海量历史数据归档清理不再需要delete全表，元数据操作秒级完成，不会产生大量undo/redo。

3. **并行处理**：查询、DML、备份恢复可以以分区为粒度做并行，适配64G内存8CPU多核算力。

4. **存储隔离**：不同分区可以放置不同表空间，冷热数据分开存储，历史冷数据可以放到低成本存储介质。

>分区表不是多功能优化手段，如果业务SQLwhere条件不带分区键，会发生全分区扫描，无法发挥分区裁剪优势，反而带来额外字典开销。网上搜索风哥教程可以学习全套数据库教程

### 1.2 Oracle19c各类分区类型原理

1. **范围分区RANGE**：最常用，依据字段数值、日期范围划分分区，适合时序业务，例如按月份、按时间归档交易流水。

2. **列表分区LIST**：依据字段离散枚举值分区，例如业务状态、地区编码，每一个分区对应一组固定枚举值。

3. **哈希分区HASH**：依据哈希算法打散数据，数据均匀分布各个分区，没有明确范围边界，适合无明显时间维度，需要均匀打散大表IO压力的场景。

4. **复合分区COMPOSITE**：两级分区，一级分区基础之上再设置子分区，例如RANGE‑LIST、RANGE‑HASH、LIST‑HASH，数据仓库事实表大量使用复合分区。

5. **Interval间隔分区**：RANGE分区的扩展，当插入数据超出已有分区边界，数据库**自动生成新分区**，不需要DBA手工定期add partition，时序业务首选。Interval分区键只能是DATE或者NUMBER类型。

6. **虚拟列分区**：基于表达式计算虚拟列做分区键，业务表不需要增加真实物理字段，直接使用字段运算结果做分区。

7. **系统分区SYSTEM**：不由数据库自动路由数据，DBA必须显示指定partition子句插入数据，数据完全由应用控制，使用场景极少。

8. **参考分区REFERENCE**：子表跟随父表外键做分区，子表不需要重复存储分区键，父子表分区一一对应，主从业务表使用。风哥数据库教程 itpux‑com

### 1.3 本地分区索引LOCAL与全局分区索引GLOBAL

1. **本地分区索引LOCAL**：索引分区与表分区一一对应，一个表分区对应一个索引分区。表分区做drop、truncate操作，只会影响对应索引分区，其余索引分区保持可用，不容易出现索引全部失效。OLTP、数据仓库环境优先选用本地索引。

2. **全局索引GLOBAL**：全局索引独立于表分区，拥有自己的分区规则，和表分区不需要一一对应。**分区执行drop、truncate、split等维护操作之后，全局索引会变为UNUSABLE不可用状态，需要重建索引；可以使用UPDATE GLOBAL INDEXES子句，维护操作同步更新全局索引，但会带来额外性能开销**。

>生产重大风险：如果忘记维护全局索引，索引处于UNUSABLE，查询直接报错，很多分区故障来源于全局索引管理不当。

### 1.4 分区维护操作基础理论

分区维护操作分为DDL元数据操作与数据操作。drop、truncate partition属于元数据操作，几乎不产生redo；exchange partition分区交换是元数据交换，不会移动数据块，秒级完成大表数据加载。

`ENABLE ROW MOVEMENT`：当更新分区键字段，行数据需要迁移到另外一个分区，必须开启行移动，否则update直接报错。默认关闭。

### 1.5 普通表转为分区表五大方案对比

1. **方案一：CTAS创建分区表+insert插入数据**：简单，会锁原表，业务必须停机，适合离线测试库。

2. **方案二：交换分区exchange partition**：新建空分区表，创建中间普通表，交换分区；适合可以分批次迁移历史数据场景。

3. **方案三：dbms_redefinition在线重定义**：业务不停机在线转换，支持24*7业务，需要足够双倍表空间，生产最常用。

4. **方案四：19c ALTER TABLE … MODIFY PARTITION BY … ONLINE**：19c新特性，online直接修改表定义转为分区表，语法简洁，但版本有要求。

5. **方案五：expdp/impdp数据泵导出导入**：适合跨库迁移，停机窗口。

### 1.6 分区统计信息管理

分区表统计分为表级别全局统计、分区级别统计、子分区级别统计。收集统计信息granularity参数控制收集粒度：ALL/ GLOBAL /PARTITION /SUBPARTITION。只收集全局统计，会丢失各个分区数据分布直方图，容易造成分区内基数估算错误。数据仓库分区表建议granularity=>'ALL'。

## 二、实战操作演练

本套风哥教程全部实战操作，操作主机`fgedu‑net‑cn`，数据库`fgedudb`，业务用户`fgedu`，目录`/fgedudb`，硬件规格64G内存8CPU。

>环境说明：操作系统登录oracle用户，设置环境变量指向`fgedudb`实例；分区表DDL操作大部分需要create table权限。

### 2.1 操作系统与数据库环境校验

#### 2.1.1操作系统层面检查（主机fgedu‑net‑cn）

```bash

#确认主机名称

hostname

#确认ORACLE_HOME路径全部指向/fgedudb

echo $ORACLE_HOME

#查看内存硬件，确认64G内存

free -h

#查看CPU，确认8CPU

lscpu

#确认表空间数据文件目录

ls -ld /fgedudb/oradata/fgedudb

```

校验输出：hostname输出`fgedu‑net‑cn`，总内存64G，逻辑CPU数量8。

#### 2.1.2 数据库关键初始化参数确认（64G内存8CPU规格）

登录`sqlplus / as sysdba`查看关键参数

```sql

show parameter memory_target;

show parameter sga_target;

show parameter pga_aggregate_target;

show parameter parallel_max_servers;

show parameter statistics_level;

```

适配64G内存主机spfile标准配置

```sql

alter system set memory_max_target=48G scope=spfile;

alter system set memory_target=48G scope=spfile;

alter system set parallel_max_servers=32 scope=spfile;

alter system set statistics_level=TYPICAL scope=spfile;

```

>parallel_max_servers适配8CPU，分区并行查询、并行DML依赖该参数。重启实例spfile生效。

#### 2.1.3 用户权限准备

```sql

create user fgedu identified by fgedudb default tablespace users temporary tablespace temp;

grant connect,resource to fgedu;

grant select_catalog_role to fgedu;

grant create any table,alter any table,drop any table to fgedu;

grant execute on dbms_redefinition to fgedu;

grant execute on dbms_stats to fgedu;

```

### 2.2 各类分区表创建实战

切换fgedu用户，演示RANGE、LIST、HASH、INTERVAL、复合分区、虚拟列分区。

```sql

conn fgedu/fgedudb@fgedudb

```

#### 2.2.1 RANGE范围分区（按交易时间）

```sql

CREATE TABLE t_sales_range (

    sale_id NUMBER,

    sale_date DATE,

    cust_id NUMBER,

    amount NUMBER(12,2)

)

PARTITION BY RANGE (sale_date)

(

PARTITION p202401 VALUES LESS THAN (TO_DATE('2024‑02‑01','YYYY‑MM‑DD')),

PARTITION p202402 VALUES LESS THAN (TO_DATE('2024‑03‑01','YYYY‑MM‑DD')),

PARTITION p202403 VALUES LESS THAN (TO_DATE('2024‑04‑01','YYYY‑MM‑DD'))

)

ENABLE ROW MOVEMENT;

```

#### 2.2.2 INTERVAL间隔自动分区（时序业务，自动生成新分区）

```sql

CREATE TABLE t_sales_interval (

    sale_id NUMBER,

    sale_date DATE,

    cust_id NUMBER,

    amount NUMBER(12,2)

)

PARTITION BY RANGE (sale_date)

INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))

(

PARTITION p_init VALUES LESS THAN (TO_DATE('2024‑01‑01','YYYY‑MM‑DD'))

)

ENABLE ROW MOVEMENT;

--插入超过初始边界日期，数据库自动新建分区

INSERT INTO t_sales_interval values(1,SYSDATE,100,2000);

commit;

```

#### 2.2.3 LIST列表分区，按业务状态

```sql

CREATE TABLE t_order_list (

    order_id NUMBER,

    order_status VARCHAR2(20),

    cust_id NUMBER,

    create_time DATE

)

PARTITION BY LIST (order_status)

(

PARTITION p_new VALUES ('NEW'),

PARTITION p_pay VALUES ('PAY'),

PARTITION p_finish VALUES ('FINISH'),

PARTITION p_other VALUES (DEFAULT)

);

```

#### 2.2.4 HASH哈希分区，打散IO压力

```sql

CREATE TABLE t_big_hash(

    id NUMBER,

    info VARCHAR2(200),

    create_dt DATE

)

PARTITION BY HASH(id)

PARTITIONS 4;

```

#### 2.2.5 复合分区 RANGE‑LIST 一级时间，二级状态

```sql

CREATE TABLE t_comp_rl (

    sale_id NUMBER,

    sale_date DATE,

    order_status VARCHAR2(20),

    amount NUMBER(12,2)

)

PARTITION BY RANGE(sale_date)

SUBPARTITION BY LIST(order_status)

SUBPARTITION TEMPLATE(

    SUBPARTITION sp_new VALUES('NEW'),

    SUBPARTITION sp_pay VALUES('PAY'),

    SUBPARTITION sp_other VALUES(DEFAULT)

)

(

PARTITION p202401 VALUES LESS THAN(TO_DATE('2024‑02‑01','YYYY‑MM‑DD')),

PARTITION p202402 VALUES LESS THAN(TO_DATE('2024‑03‑01','YYYY‑MM‑DD'))

);

```

#### 2.2.6 虚拟列分区

```sql

CREATE TABLE t_virtual_part(

    id NUMBER,

    province_code VARCHAR2(10),

    amount NUMBER

)

PARTITION BY RANGE (MOD(id,10))

(

PARTITION p0 VALUES LESS THAN(1),

PARTITION p1 VALUES LESS THAN(2),

PARTITION p_max VALUES LESS THAN(MAXVALUE)

);

```

### 2.3 本地LOCAL索引、全局GLOBAL分区索引创建实操

#### 2.3.1 创建本地分区索引

```sql

CREATE INDEX idx_sales_local ON t_sales_range(sale_date) LOCAL;

```

#### 2.3.2 创建全局分区索引

```sql

CREATE INDEX idx_sales_global ON t_sales_range(sale_id)

GLOBAL PARTITION BY RANGE(sale_id)

(

PARTITION g_p1 VALUES LESS THAN(100000),

PARTITION g_p2 VALUES LESS THAN(200000),

PARTITION g_pmax VALUES LESS THAN(MAXVALUE)

);

```

>查询索引是否不可用

```sql

select index_name,partition_name,status from user_ind_partitions;

```

### 2.4 分区表日常维护DDL完整实操

#### 2.4.1 新增分区（interval分区不需要add partition）

```sql

ALTER TABLE t_sales_range ADD PARTITION p202404

VALUES LESS THAN(TO_DATE('2024‑05‑01','YYYY‑MM‑DD'));

```

#### 2.4.2 删除分区，UPDATE GLOBAL INDEXES避免全局索引失效

```sql

ALTER TABLE t_sales_range DROP PARTITION p202401 UPDATE GLOBAL INDEXES;

```

#### 2.4.3 SPLIT拆分分区，拆分maxvalue分区

```sql

ALTER TABLE t_sales_range SPLIT PARTITION p202403

AT(TO_DATE('2024‑03‑15','YYYY‑MM‑DD'))

INTO(PARTITION p202403_1,PARTITION p202403_2)

UPDATE GLOBAL INDEXES;

```

#### 2.4.4 MERGE合并两个相邻分区

```sql

ALTER TABLE t_sales_range MERGE PARTITIONS p202403_1,p202403_2

INTO PARTITION p202403_merge UPDATE GLOBAL INDEXES;

```

#### 2.4.5 TRUNCATE截断分区（快速清空分区数据）

```sql

ALTER TABLE t_sales_range TRUNCATE PARTITION p202402 UPDATE GLOBAL INDEXES;

```

#### 2.4.6 MOVE移动分区到其他表空间

```sql

ALTER TABLE t_sales_range MOVE PARTITION p202403 TABLESPACE users UPDATE GLOBAL INDEXES;

```

#### 2.4.7 RENAME重命名分区

```sql

ALTER TABLE t_sales_range RENAME PARTITION p202403_merge TO p202403_new;

```

#### 2.4.8 EXCHANGE PARTITION分区交换，秒级加载数据

```sql

--创建普通中间表

create table t_stg as select * from t_sales_range where 1=0;

--向中间表插入数据

insert into t_stg select * from t_sales_range where sale_date>=TO_DATE('2024‑03‑01','YYYY‑MM‑DD');

commit;

--交换分区，元数据操作，几乎不IO拷贝数据

ALTER TABLE t_sales_range EXCHANGE PARTITION p202403_new WITH TABLE t_stg WITHOUT VALIDATION;

```

### 2.5 分区索引维护操作实操

```sql

--重建不可用本地索引分区

ALTER INDEX idx_sales_local REBUILD PARTITION p202403;

--重建全局索引

ALTER INDEX idx_sales_global REBUILD;

--重命名索引分区

ALTER INDEX idx_sales_local RENAME PARTITION p202403 TO idx_p202403;

```

### 2.6 分区相关数据字典查询

```sql

--查看表分区信息

select table_name,partition_name,high_value,tablespace_name from user_tab_partitions;

--查看子分区（复合分区）

select table_name,partition_name,subpartition_name from user_tab_subpartitions;

--查看索引分区状态

select index_name,partition_name,status from user_ind_partitions;

--查看分区裁剪，explain plan看Pstart Pstop

explain plan for select * from t_sales_range where sale_date=TO_DATE('2024‑02‑05','YYYY‑MM‑DD');

select * from table(dbms_xplan.display());

```

>执行计划中`Pstart/Pstop`不是MAXVALUE代表发生分区裁剪；Pstart:Pstop为MAXVALUE代表扫描全部分区，裁剪失效。网上搜索风哥教程可以学习全套数据库教程

### 2.7 分区表统计信息收集实操

granularity参数控制统计粒度，ALL收集全局、分区、子分区统计。

```sql

exec dbms_stats.gather_table_stats(

ownname=>'FGEDU',

tabname=>'T_SALES_RANGE',

estimate_percent=>dbms_stats.auto_sample_size,

method_opt=>'FOR ALL COLUMNS SIZE AUTO',

degree=>4,

cascade=>true,

granularity=>'ALL'

);

```

### 2.8 普通表转分区表五大方案演示

#### 方案1：CTAS（停机，业务不可用）

```sql

create table t_part_ctas partition by range(id)

(partition p1 values less than (1000),partition p2 values less than(maxvalue))

as select * from t_big_hash;

```

#### 方案2：19c ONLINE直接修改表定义转为分区表

```sql

alter table t_big_hash modify

partition by range(id)

(partition p_1 values less than(1000),

partition p_max values less than(maxvalue))

ONLINE UPDATE INDEXES;

```

#### 方案3：DBMS_REDEFINITION在线重定义（生产不停机重点案例）风哥数据库教程 itpux‑com

>前置条件：原表要有主键；SYS/SYSTEM表不能做在线重定义；要有足够表空间存放双倍数据。

```sql

conn fgedu/fgedudb@fgedudb

--1.准备源普通表

create table t_origin_big(id number primary key,sale_date date,amt number);

insert into t_origin_big select rownum,sysdate‑mod(rownum,365),rownum from dual connect by rownum<=200000;

commit;

--2.创建中间过渡分区表（只建结构，无数据）

CREATE TABLE t_interim_part(

id number primary key,

sale_date date,

amt number

)

PARTITION BY RANGE(sale_date)

INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))

(PARTITION p_start VALUES LESS THAN (TO_DATE('2023‑01‑01','YYYY‑MM‑DD')));

--3.校验是否满足重定义条件

DECLARE

v_ret NUMBER;

BEGIN

v_ret:=dbms_redefinition.can_redef_table(uname=>'FGEDU',tname=>'T_ORIGIN_BIG');

dbms_output.put_line('校验返回:'||v_ret);

END;

/

--4.启动在线重定义，源表、中间过渡表

BEGIN

dbms_redefinition.start_redef_table(

uname=>'FGEDU',

orig_table=>'T_ORIGIN_BIG',

int_table=>'T_INTERIM_PART'

);

END;

/

--可选：同步增量，多次执行同步中间表捕获业务新增DML

BEGIN

dbms_redefinition.sync_interim_table(uname=>'FGEDU',orig_table=>'T_ORIGIN_BIG',int_table=>'T_INTERIM_PART');

END;

/

--5.完成重定义，短暂排他锁切换元数据

BEGIN

dbms_redefinition.finish_redef_table(uname=>'FGEDU',orig_table=>'T_ORIGIN_BIG',int_table=>'T_INTERIM_PART');

END;

/

--完成之后：T_ORIGIN_BIG已经变成分区表，t_interim_part变为原来普通表，可以drop清理

drop table t_interim_part purge;

```

#### 方案4：Exchange partition交换分区分批迁移

1. 新建目标空分区表；

2. 建立多张中间普通表，分批导出原表数据；

3. 循环执行exchange partition把中间表数据交换进入各个分区。适合超大表分批迁移，减少锁压力。

#### 方案5：数据泵expdp/impdp导出导入，停机迁移

```bash

#导出原表，路径/fgedudb/dump

expdp fgedu/fgedudb@fgedudb directory=DATA_PUMP_DIR dumpfile=part_exp.dmp tables=T_ORIGIN_BIG logfile=part_exp.log

#导入目标分区表

impdp fgedu/fgedudb@fgedudb directory=DATA_PUMP_DIR dumpfile=part_exp.dmp tables=T_ORIGIN_BIG REMAP_TABLE=T_ORIGIN_BIG:T_PART_TARGET logfile=part_imp.log

```

### 2.9 分区表生产故障排查流程

1. SQL性能差，首先查看执行计划Pstart、Pstop，确认**分区裁剪是否生效**，Pstart=MAXVALUE代表全分区扫描，需要优化where条件带上分区键。

2. 分区维护操作之后查询`user_ind_partitions`，检查索引分区status，确认是否出现UNUSABLE；

3. 全局索引操作务必带上`UPDATE GLOBAL INDEXES`，或者维护完成之后重建全局索引；

4. 查看分区表统计，检查granularity粒度，确认分区级统计、直方图是否收集；

5. 更新分区键字段报错ORA‑14402，检查是否开启`ENABLE ROW MOVEMENT`；

6. interval分区插入数据不自动生成，检查分区键字段类型只能是DATE/NUMBER；

7. 在线重定义失败，调用`dbms_redefinition.abort_redef_table`终止操作，释放资源。

## 三、风哥针对本文总结

本套风哥教程完整覆盖Oracle分区表整套知识，从分区表原理、8类分区类型、本地/全局分区索引，全套分区维护DDL，分区数据字典、统计信息管理，普通表转分区五大方案，dbms_redefinition在线重定义完整实操、生产故障排查流程。

1. 分区表核心收益来自**分区裁剪**，业务SQL查询条件尽量带上分区键；如果大量业务SQL不带分区键，分区表不会带来性能收益，不建议使用分区。

2. 优先使用**本地LOCAL分区索引**；全局GLOBAL索引维护成本很高，drop/truncate/split分区极易导致索引UNUSABLE，维护操作必须带上`UPDATE GLOBAL INDEXES`，操作完成务必校验索引状态。

3. 时序流水业务优先选择`INTERVAL`间隔分区，数据库自动生成新分区，省去DBA定期手工add partition维护；注意interval分区键只能是DATE或者NUMBER类型。

4. 分区交换`EXCHANGE PARTITION`是元数据操作，几乎不移动数据块，海量数据加载优先考虑exchange partition，性能远高于insert；使用WITHOUT VALIDATION需要业务保证数据符合分区边界，避免脏数据。

5. 普通大表转分区表：24*7不停机业务优先`DBMS_REDEFINITION`在线重定义，注意需要至少双倍表空间；Oracle19c可以尝试ALTER TABLE ... MODIFY PARTITION BY ... ONLINE；CTAS只适合可以停机的离线场景。

6. 分区表统计信息收集，granularity建议设置为`ALL`，同时收集全局、分区、子分区统计与直方图；只收集全局统计会造成分区内基数估算错误，引发执行计划漂移。

7. 生产分区维护DDL（drop、split、merge、truncate、move），只要存在全局索引，必须评估`UPDATE GLOBAL INDEXES`带来的开销，业务高峰谨慎执行，避开业务峰值窗口。

8. 设计分区的时候，合理规划分区大小，避免产生大量极小分区，分区数量过多会加重数据字典负载，影响解析性能。