当前位置： 代码网 > it编程>数据库>Oracle > Oracle普通表改造分区表的方法汇总

Oracle普通表改造分区表的方法汇总

2026年09月06日 • Oracle •我要评论
一、改造方法总结1、dbms_redefinition在线重定义方式2、create table nologging parallel as select /*+ parallel(a 4) */ 并

一、改造方法总结

1、dbms_redefinition在线重定义方式

2、create table nologging parallel as select /*+ parallel(a 4) */ 并发快速创建新表（分区形式），然后表名互换

3、expdp/impdp方法，非在线的改造方式

4、直接alter table xxx modify 方式将非分区表改为分区表，12.2以上版本才有的功能。

5、将表交换为一个分区表中的一个分区，然后split这个分区，然后表名互换

二、五种改造方法对比

方法核心原理优点缺点核心要点与注意事项
方法一：dbms_redefinition 在线重定义创建一个新的分区表（中间表），通过内置包将源表数据在线拷贝至中间表，并在切换时通过原子操作交换表名。真正的在线操作，业务几乎无感知；
支持回退 (abort_redef_table)；
可多次同步 (sync_interim_table) 控制切换时间；
支持列映射、列转换。		需要额外1倍表空间的临时表空间；
源表必须有主键或可用rowid；
操作步骤较多，复杂度高；
主键列不能修改；
无法采用 nologging；
完成后必须手动收集统计信息。		需注意：
• 必须在同一用户下进行；
• sys / system 用户下的表不支持；
• 不支持含 long、bfile、域索引的表；
• 不支持含物化视图日志的表（需先删除）；
• 重定义期间禁止 flashback table/query；
• 表列的默认值需提前在中间表手工创建。
方法二：create table ... as select (ctas)利用 create table ... nologging parallel as select 并发创建一个新的分区表，然后通过表名互换完成切换。创建速度快，nologging + parallel 产生的日志最少；
语法简单，易于理解；
不受主键、物化视图日志等限制。		业务需停止（不能有dml）；
必须先将表设为只读（alter table ... read only）；
所有依赖对象（索引、约束、触发器、权限、视图、存储过程等）需全部手工重建，工作量大且容易遗漏；
完成后需手动收集统计信息。		建议：
• 在11g及以上版本可设置表为只读；
• 建议将重建依赖对象的脚本提前准备好并测试通过；
• 适合在维护窗口进行。
方法三：数据泵 (expdp/impdp)通过 expdp 导出源表数据，再通过 impdp 导入到一个预先创建好的分区表中。速度相对较快；
语法简单；
不受主键、物化视图日志等限制。		业务需停止（不能有dml）；
必须先将表设为只读；
所有依赖对象需全部手工重建，工作量大；
完成后需手动收集统计信息。		建议：
• 适合数据量极大的场景；
• 可配合 network_link 参数实现远程迁移；
• 与方法二类似，属于非在线方式。
方法四：alter table ... modify (12.2+)oracle 12.2 及以上版本提供的原生在线ddl，直接将普通表转换为分区表，并可选择转换时对索引的处理方式。语法最简洁，一条ddl完成；
依赖对象（索引、约束、触发器、默认值、权限、视图等）全部自动保留；
无需额外表空间；
支持 online 模式，业务几乎无感知。		仅限 oracle 12.2+；
不支持域索引；
不支持含物化视图日志的表；
大表会产生大量 redo 日志（10g以上大表需谨慎评估）；
转换后需手动收集统计信息。		关键技巧：
• 建议提前删除非必要索引，转换后再重建，以减少redo并加快速度；
• 参考分区（reference partitioning）的在线转换需 19.12+；
• 内部仍需移动数据，大表需充分测试。
方法五：exchange + split 分区交换创建一个单分区表，通过 exchange partition 与普通表交换（仅改数据字典），再通过 split partition 将这个大分区拆分为多个分区。exchange 操作极快（仅修改数据字典，不物理移动数据）；
不需要额外的数据复制。		split 大分区时耗时非常长（需移动数据）；
所有依赖对象需全部手工重建，工作量大；
操作步骤多，容易出错；
转换后需手动收集统计信息。		现状：
• 此方法步骤繁琐且存在性能瓶颈；
• 如今已基本不使用，已被方法一或方法四取代。

三、改造方法选择推荐

推荐场景推荐方法
生产环境，业务不能停，且数据库版本在 12.2+方法四 (alter table ... modify online) — 首选，最省心
生产环境，业务不能停，但数据库版本低于 12.2方法一 (dbms_redefinition) — 唯一可靠的在线方式
有维护窗口，业务可停，表数据量巨大 (tb级)方法二 (ctas) 或 方法三 (expdp/impdp) — 速度最快，配合 nologging + 并行，日志最少
追求操作最简，版本满足要求，表数据量中等方法四 — 一条命令搞定所有依赖对象
临时测试环境，快速验证分区效果方法二 (ctas) — 最直接方便

四、alter table modify改造语法

alter table ... modify 命令是从 oracle 12c r2（12.2） 版本开始引入的特性，用于直接将一个普通的堆组织表（heap-organized table）转换为分区表

特别注意：对于大表，建议提前删除非必要索引，转换后重建，减少redo量，加快整体速度

alter table table_name modify
    partition by { 
        range (column_list) [ interval (expr) ] ( partition_definition [, partition_definition ]... ) |
        list (column) ( partition_definition [, partition_definition ]... ) |
        hash (column) { partitions num | ( partition_definition [, partition_definition ]... ) }
    }
    [ subpartition by ... ] -- 可选的子分区定义
    [ online ]              -- 可选，允许在线转换
    [ update indexes (      -- 可选，用于精细控制索引转换
        index_name { local | global [ partition_definition ] } [, ...]
    ) ];

语法组成部分详解

partition by 子句（必需）

定义表的分区策略，与 create table 语法类似。

分区类型语法示例说明
范围分区 (range)partition by range (id) (partition p1 values less than (100), ...)最常用，适用于按日期、id等有序字段分区。
间隔分区 (interval)partition by range (created_date) interval (numtodsinterval(1,'day')) (partition p_init values less than (date '2023-01-01'))range分区的扩展，可自动创建新分区。
列表分区 (list)partition by list (region) (partition p1 values ('east','west'), ...)适用于枚举值字段。
哈希分区 (hash)partition by hash (id) partitions 4适用于数据均匀分布的场景。

subpartition by 子句（可选）

用于创建复合分区表，对每个主分区再分子分区，常见组合有 range-hash 、range-list 等。

alter table sales modify
    partition by range (sale_date) (
        partition p1 values less than (date '2023-01-01'),
        partition p2 values less than (date '2023-02-01')
    )
    subpartition by hash (customer_id) subpartitions 8; -- 每个主分区再分为8个哈希子分区

online 关键字（强烈推荐）

online 关键字允许在不阻塞dml操作的情况下在线转换表。其内部机制复杂，会创建日志表记录变更并通过批量迁移完成转换。

alter table your_table modify partition by range (id) (...) online;

update indexes 子句（关键）

此子句用于精细控制表上现有索引的转换方式。如果不指定，oracle会按默认规则转换，一定要慎重，尽量避免这样，默认不可控。

如果确实有索引需要转换，强烈建议明确指定索引的转换方式！！！

update indexes (
    idx_name1 local,                 -- 转为本地分区索引
    idx_name2 global,                -- 转为非分区全局索引
    idx_name3 global partition by range (col) (partition p_idx values less than (maxvalue))
)

简单示例

1）修改为普通分区类型，类似如下
alter table employees_convert modify
  partition by range (employee_id) interval (100)
  ( partition p1 values less than (100),
    partition p2 values less than (500)
   ) online   -online 表示在线，不指定为offline，锁表
  update indexes
 ( idx1_salary local,
   idx2_emp_id global partition by range (employee_id)
  ( partition ip1 values less than (maxvalue))
 );
 
 
2）修改为subpartition分区，类似如下
alter table test_tab modify
partition by range (created_date) subpartition by hash (id)(
partition test_tab_2021 values less than (to_date('01-jan-2022','dd-mon-yyyy')) (
subpartition test_tab_sub_part_2021_1,
subpartition test_tab_sub_part_2021_2,
subpartition test_tab_sub_part_2021_3,
subpartition test_tab_sub_part_2021_4
),
partition test_tab_2022 values less than (to_date('01-jan-2023','dd-mon-yyyy')) (
subpartition test_tab_sub_part_2022_1,
subpartition test_tab_sub_part_2022_2,
subpartition test_tab_sub_part_2022_3,
subpartition test_tab_sub_part_2022_4
),
partition test_tab_2023 values less than (to_date('01-jan-2024','dd-mon-yyyy')) (
subpartition test_tab_sub_part_2023_1,
subpartition test_tab_sub_part_2023_2,
subpartition test_tab_sub_part_2023_3,
subpartition test_tab_sub_part_2023_4
) )
online
update indexes
(
test_tab_pk global,
test_tab_created_date_idx local
);

五、测试案例

1. 实验环境准备

1.1 创建测试表并加载大量数据

-- 创建测试表 t，包含 500 万行数据
create table t (
    id          number,
    col1        number,
    col2        number,
    col3        number,
    col4        number,
    padding     varchar2(100)
);

-- 插入 200 万行
insert /*+ append */ into t
select level,
       mod(level, 1000),
       mod(level, 500),
       round(dbms_random.value(1, 10000)),
       round(dbms_random.value(1, 10000)),
       lpad('x', 100, 'x')
from   dual
connect by level <= 2000000;

commit;

-- 收集统计信息
exec dbms_stats.gather_table_stats(user, 't');

1.2 创建多种类型的索引

-- 1. 前缀索引（索引列包含分区键 id）
create index idx_prefix on t(id);

-- 2. 非前缀普通索引（索引列不包含分区键）
create index idx_nonprefix on t(col1, col2);

-- 3. 普通索引
create  index idx_col3 on t(col3);

-- 4. 唯一索引（包含分区键，用于观察全局唯一性约束）
create unique index idx_unique on t(id, col4);

1.3 检查索引初始状态

set linesize 200 pagesize 999
col index_name format a15
col index_type format a15
col uniqueness format a15
select index_name, index_type, uniqueness, partitioned
from user_indexes
where table_name = 't';

index_name      index_type      uniqueness      partition
--------------- --------------- --------------- ---------
idx_unique      normal          unique          no
idx_col3        normal          nonunique       no
idx_nonprefix   normal          nonunique       no
idx_prefix      normal          nonunique       no

2. 场景一：默认行为（省略update indexes）

2.1 执行分区转换

-- 不指定 update indexes，由 oracle 自动决定索引转换方式
set timing on
alter table t modify
    partition by range (id) (
        partition p1 values less than (1000000),
        partition p2 values less than (2000000),
        partition p3 values less than (3000000),
        partition p4 values less than (4000000),
        partition p5 values less than (maxvalue)
    );

2.2 检查转换后的索引状态

-- 查看索引是否分区以及类型
select index_name, partitioned, status
from user_indexes
where table_name = 't';

index_name      par status
--------------- --- --------
idx_prefix      yes n/a
idx_nonprefix   no  valid
idx_col3        no  valid
idx_unique      yes n/a

-- 查看分区索引的各分区状态
col partition_name format a30
select index_name, partition_name, status
from user_ind_partitions
where index_name in ('idx_prefix', 'idx_unique')
order by index_name, partition_name;

index_name      partition_name                 status
--------------- ------------------------------ --------
idx_prefix      p1                             usable
idx_prefix      p2                             usable
idx_prefix      p3                             usable
idx_prefix      p4                             usable
idx_prefix      p5                             usable
idx_unique      p1                             usable
idx_unique      p2                             usable
idx_unique      p3                             usable
idx_unique      p4                             usable
idx_unique      p5                             usable

2.3 结果

不指定（省略 update indexes），oracle会按默认规则转换表上的索引！

索引名分区状态说明
idx_prefixyes因为包含分区键 id，自动转换为本地分区索引
idx_nonprefixno不包含分区键，自动保留为非分区全局索引
idx_col3no不包含分区键，自动保留为非分区全局索引
idx_uniqueyes唯一索引包含分区键，自动转换为本地分区索引（但若包含唯一约束，需注意是否有跨分区唯一性）

3. 场景二：各种选项下的redo日志产生量

主要考虑online和非online模式，以及是否更新索引，这几种情况下的redo日志产生量

0、准备工作，重建初始表（与场景一完全一致）

为避免干扰，重新创建相同的表结构和数据，或者将表改回普通表并重新创建索引。

-- 回退方法（若允许）
drop table t purge;
-- 重新执行 1.1 和 1.2 步骤重建
-- 索引是否创建，根据场景来决定

1、有索引，非online模式，redo日志产生量

-- 不指定 update indexes，由 oracle 自动决定索引转换方式
set timing on
alter table t modify
    partition by range (id) (
        partition p1 values less than (1000000),
        partition p2 values less than (2000000),
        partition p3 values less than (3000000),
        partition p4 values less than (4000000),
        partition p5 values less than (maxvalue)
    );
    
col name format a20
select a.name,b.value 
from v$statname a,v$mystat  b
where a.statistic# = b.statistic# and a.name='redo size';


非online模式下，有索引
name                      value
-------------------- ----------
redo size               1908180

2、无索引，非online模式，redo日志产生量

set timing on
alter table t modify
    partition by range (id) (
        partition p1 values less than (1000000),
        partition p2 values less than (2000000),
        partition p3 values less than (3000000),
        partition p4 values less than (4000000),
        partition p5 values less than (maxvalue)
    );
    
col name format a20
select a.name,b.value 
from v$statname a,v$mystat  b
where a.statistic# = b.statistic# and a.name='redo size';

非online模式下，无索引
name                      value
-------------------- ----------
redo size                683044

3、无索引，online模式，redo日志产生量

set linesize 200 pagesize 999
col index_name format a15
col index_type format a15
col uniqueness format a15
col name format a20
select a.name,b.value 
from v$statname a,v$mystat  b
where a.statistic# = b.statistic# and a.name='redo size';

alter table t modify
    partition by range (id) (
        partition p1 values less than (1000000),
        partition p2 values less than (2000000),
        partition p3 values less than (3000000),
        partition p4 values less than (4000000),
        partition p5 values less than (maxvalue)
    ) online;
select a.name,b.value 
from v$statname a,v$mystat  b
where a.statistic# = b.statistic# and a.name='redo size';    
    

online模式下，无索引
name                      value
-------------------- ----------
redo size               1327032

4、有索引，online模式，redo日志产生量

set linesize 200 pagesize 999
col index_name format a15
col index_type format a15
col uniqueness format a15
col name format a20
select a.name,b.value 
from v$statname a,v$mystat  b
where a.statistic# = b.statistic# and a.name='redo size';

alter table t modify
    partition by range (id) (
        partition p1 values less than (1000000),
        partition p2 values less than (2000000),
        partition p3 values less than (3000000),
        partition p4 values less than (4000000),
        partition p5 values less than (maxvalue)
    ) online
    update indexes (
        idx_prefix local,
        idx_nonprefix global,
        idx_col3 local,
        idx_unique global
    );
    
select a.name,b.value 
from v$statname a,v$mystat  b
where a.statistic# = b.statistic# and a.name='redo size';

online模式下，有索引
name                      value
-------------------- ----------
redo size               2528856

结果对比：

modify改造模式redo日志量执行速度
非online模式下，有索引1908180第三
非online模式下，无索引683044第一
online模式下，无索引1327032第二
online模式下，有索引2528856第四

结论：如果条件允许，建议提前删除非必要索引，转换后再重建，以减少redo并加快整体速度。

以上就是oracle普通表改造分区表的方法汇总的详细内容，更多关于oracle普通表改造分区表的资料请关注代码网其它相关文章！

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2026  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com