Oracle在线重定义实战：亿级订单表分区改造与性能提升

在真实的业务场景中，我们常常会遇到这样的情况：初期设计的普通表，随着数据量的爆炸式增长（例如达到千万甚至亿级），查询性能开始急剧下降。通过分区表进行数据裁剪，是提升查询效率、优化 I/O 的经典手段。但如何在不中断业务、不影响数据写入的前提下，将一张存量巨大的生产表安全地转换为分区表呢？Oracle 的在线重定义（Online Redefinition）功能为此提供了完美的解决方案。

本文将以一张名为 T_TEST、记录数达一亿行的订单相关表为例，详细演示如何使用在线重定义，将其从普通表转换为按时间（CREATED_DATE）字段进行范围分区的分区表。

生产环境实施前，请务必注意以下事项：

1. 若数据库为双节点架构，只需在其中一个节点的数据库上执行操作。
2. 部分命令执行时间较长，一旦开始执行，请勿中断。同时，每条命令执行完毕后，不能再次重复执行。
3. 执行命令的数据库用户需要具备足够权限，例如 SYS 或 SYSTEM 用户。

第一步：准备环境与确认原表结构

首先，我们明确要操作的原表 T_TEST，其结构如下：

CREATE TABLE "XXX"."T_TEST" 
   (    "ID" VARCHAR2(20) NOT NULL ENABLE, 
    "A_ID" VARCHAR2(20), 
    "TYPE" NUMBER, 
    "CHANNEL" VARCHAR2(10), 
    "SOURCE" VARCHAR2(20), 
    "STATUS" NUMBER, 
    "CURRENCY" CHAR(3), 
    "FARE_PRICE" NUMBER(8,2), 
    "ACTUAL_PRICE" NUMBER(8,2), 
    "TAX" NUMBER(8,2), 
    "COMMISSION" NUMBER(8,2), 
    "DEDUCTED" NUMBER, 
    "CONTACT_NAME" VARCHAR2(200), 
    "CONTACT_PHONE" VARCHAR2(20), 
    "CONTACT_PHONE_COUNTRY" CHAR(2), 
    "CONTACT_EMAIL" VARCHAR2(50), 
    "CONTACT_NAME2" VARCHAR2(200), 
    "CONTACT_PHONE2" VARCHAR2(20), 
    "CONTACT_PHONE_COUNTRY2" CHAR(2), 
    "CONTACT_EMAIL2" VARCHAR2(50), 
    "REMARK" VARCHAR2(500), 
    "C_ID" VARCHAR2(20), 
    "CREATED_DATE" DATE, 
    "UPDATE_ID" VARCHAR2(20), 
    "LAST_UPDATE" DATE, 
    "VERSION" NUMBER, 
    "DELETED" NUMBER, 
    "DESCRIPTION" VARCHAR2(1000), 
    "ORDERER" VARCHAR2(20), 
    "FINAL_PAYMENT_START_DATE" DATE, 
    "FINAL_PAYMENT_END_DATE" DATE, 
    "EARNEST" NUMBER(8,2), 
    "EXCHANGE_RATE" NUMBER(5,4), 
    "BAX_PRICE" NUMBER(8,2), 
     PRIMARY KEY ("ID")
   );

第二步：收集原表统计信息

在进行重定义前，建议先收集原表的统计信息，这有助于优化器在后续步骤中做出更好的决策。可以先用以下语句查看当前统计信息状态：

Select Table_Name,
Tablespace_Name,
Num_Rows,
last_analyzed
From dba_Tables
where TABLE_NAME = 'T_TEST';

然后使用 DBMS_STATS 包收集详细的统计信息：

begin
    dbms_stats.gather_table_stats(ownname => 'XXX',
    tabname => 'T_TEST',
    estimate_percent => 100,
    method_opt => 'for all indexed columns',
    cascade => true,
    degree => 2);
end;
/

第三步：创建目标分区表（中间表）

这是关键的一步。我们需要创建一个和原表结构完全相同，但带有分区定义的新表（此处命名为 T_TEST_R10）。本例将根据 CREATED_DATE 字段进行范围分区，设计从2019年到2026年的年度分区，以及2026年内的月度分区，并包含一个 MAXVALUE 分区容纳未来数据。

CREATE TABLE "XXX"."T_TEST_R10" 
   (    "ID" VARCHAR2(20) NOT NULL ENABLE, 
    "A_ID" VARCHAR2(20), 
    "TYPE" NUMBER, 
    "CHANNEL" VARCHAR2(10), 
    "SOURCE" VARCHAR2(20), 
    "STATUS" NUMBER, 
    "CURRENCY" CHAR(3), 
    "FARE_PRICE" NUMBER(8,2), 
    "ACTUAL_PRICE" NUMBER(8,2), 
    "TAX" NUMBER(8,2), 
    "COMMISSION" NUMBER(8,2), 
    "DEDUCTED" NUMBER, 
    "CONTACT_NAME" VARCHAR2(200), 
    "CONTACT_PHONE" VARCHAR2(20), 
    "CONTACT_PHONE_COUNTRY" CHAR(2), 
    "CONTACT_EMAIL" VARCHAR2(50), 
    "CONTACT_NAME2" VARCHAR2(200), 
    "CONTACT_PHONE2" VARCHAR2(20), 
    "CONTACT_PHONE_COUNTRY2" CHAR(2), 
    "CONTACT_EMAIL2" VARCHAR2(50), 
    "REMARK" VARCHAR2(500), 
    "C_ID" VARCHAR2(20), 
    "CREATED_DATE" DATE, 
    "UPDATE_ID" VARCHAR2(20), 
    "LAST_UPDATE" DATE, 
    "VERSION" NUMBER, 
    "DELETED" NUMBER, 
    "DESCRIPTION" VARCHAR2(1000), 
    "ORDERER" VARCHAR2(20), 
    "FINAL_PAYMENT_START_DATE" DATE, 
    "FINAL_PAYMENT_END_DATE" DATE, 
    "EARNEST" NUMBER(8,2), 
    "EXCHANGE_RATE" NUMBER(5,4), 
    "BAX_PRICE" NUMBER(8,2), 
     PRIMARY KEY ("ID")
   ) TABLESPACE "DCSDATA" 
   partition by range (CREATED_DATE)
   (
     partition P2019  values less than (to_date('2020-01-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P2020  values less than (to_date('2021-01-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P2021  values less than (to_date('2022-01-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P2022  values less than (to_date('2023-01-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P2023  values less than (to_date('2024-01-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P2024  values less than (to_date('2025-01-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P2025  values less than (to_date('2026-01-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202601  values less than (to_date('2026-02-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202602  values less than (to_date('2026-03-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202603  values less than (to_date('2026-04-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202604  values less than (to_date('2026-05-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202605  values less than (to_date('2026-06-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202606  values less than (to_date('2026-07-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202607  values less than (to_date('2026-08-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202608  values less than (to_date('2026-09-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202609  values less than (to_date('2026-10-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202610  values less than (to_date('2026-11-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202611  values less than (to_date('2026-12-01','YYYY-MM-DD')) tablespace DCSDATA,
     partition P202612  values less than (to_date('2027-01-01','YYYY-MM-DD')) tablespace DCSDATA,
     -- --
     partition p_max values less than (MAXVALUE)
   );

第四步：执行在线重定义操作

整个重定义过程由多个步骤组成，核心是使用 DBMS_REDEFINITION 包。

1. 检查重定义的可行性

首先验证原表是否支持在线重定义。执行以下命令，若无报错，则说明可以进行操作。

begin
    dbms_redefinition.can_redef_table('XXX','T_TEST');
end;
/

-- 没报错，说明是合理的。

2. 开始重定义

此步骤会开始将原表数据同步到中间表。建议在执行前后记录时间，以了解耗时。

select sysdate from dual;

begin
    dbms_redefinition.start_redef_table(uname => 'XXX',
    orig_table => 'T_TEST',
    int_table => 'T_TEST_R10',
    options_flag => dbms_redefinition.cons_use_pk);
end;
/

select sysdate from dual;

关于 options_flag 参数：

• dbms_redefinition.cons_use_pk：基于主键进行重定义（原表必须有主键）。
• dbms_redefinition.cons_use_rowid：基于 ROWID 进行重定义（适用于无主键的表）。

3. 复制依赖对象

将原表上的索引、触发器、约束、权限等依赖对象复制到中间表。

select sysdate from dual;

declare
num_errors pls_integer;
begin
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ( uname => 'XXX',
    orig_table => 'T_TEST',
    int_table => 'T_TEST_R10',
    copy_indexes => dbms_redefinition.cons_orig_params,
    copy_triggers => TRUE,
    copy_constraints => TRUE,
    copy_privileges => TRUE,
    ignore_errors => TRUE,
    num_errors => num_errors);
end;
/

select sysdate from dual;

4. 同步增量数据

在复制依赖对象期间，原表可能仍有数据变更。此步骤用于同步这段时间产生的增量数据。

select sysdate from dual;

begin
    dbms_redefinition.sync_interim_table(uname => 'XXX',
    orig_table => 'T_TEST',
    int_table => 'T_TEST_R10');
end;
/

select sysdate from dual;

5. （可选）创建索引

如果原表有索引但上一步复制失败或未复制，可以手动创建。如果已成功复制，可跳过。

create index XXX.i_T_TEST_R10_name on XXX.T_TEST_R10(name);

6. 收集中间表的统计信息

Select Table_Name,
Tablespace_Name,
Num_Rows,
last_analyzed
From dba_Tables
where TABLE_NAME = 'T_TEST_R10';

begin
    dbms_stats.gather_table_stats(ownname => 'XXX',
    tabname => 'T_TEST_R10',
    estimate_percent => 100,
    method_opt => 'for all indexed columns',
    cascade => true,
    granularity => 'all',
    degree => 2);
end;
/

7. 结束重定义（关键步骤）

此步骤会短暂锁表，进行最终的元数据切换。执行后，原 T_TEST 表就变成了分区表，而 T_TEST_R10 则变成了普通表。

begin
    dbms_redefinition.finish_redef_table(uname => 'XXX',
    orig_table => 'T_TEST',
    int_table => 'T_TEST_R10');
end;
/

select sysdate from dual;

切换后，可以立刻查看新表结构确认：

set long 100000
select dbms_metadata.get_ddl('TABLE','T_TEST','XXX') table_name from dual;

8. 同步表与字段注释

如果原表有注释，需要将其添加到新表中。

comment on table XXX.T_TEST is '会员信息表';

comment on column XXX.T_TEST.id is '会员编号';

第五步：转换后检查与收尾工作

1. 分区表检查

确认原表已变为分区表，而中间表已成为普通表。

select * from dba_tab_partitions where table_name='T_TEST_R10';
select * from dba_tab_partitions where table_name='T_TEST';

2. 核对表信息与数据一致性

检查表的基本信息，并确保数据记录数一致。

select * from dba_tables where table_name='T_TEST_R10';
select * from dba_tables where table_name='T_TEST';

select count(*) from XXX.T_TEST_R10;
select count(*) from XXX.T_TEST;

3. 检查数据分区分布

验证数据是否已按预想的分区规则正确分布。

select   max(ID) from XXX.T_TEST;
select count(ID) from XXX.T_TEST partition (P2019);
select count(ID) from XXX.T_TEST partition (P2020);
select count(ID) from XXX.T_TEST partition (P2021);
select count(ID) from XXX.T_TEST partition (P2022);
select count(ID) from XXX.T_TEST partition (P2023);
select count(ID) from XXX.T_TEST partition (P2024);
select count(ID) from XXX.T_TEST partition (P2025);
select count(ID) from XXX.T_TEST partition (P2026);
select count(ID) from XXX.T_TEST partition (p_max);

4. 清理临时中间表

确认无误后，可以删除临时创建的中间表 T_TEST_R10。

-- drop table XXX.T_TEST_R10;

建议先保留观察一段时间，待完全稳定后再删除。

5. 重建索引为本地分区索引

对于分区表，全局索引可能会影响分区维护操作效率。建议将非分区键上的重要索引重建为本地分区索引（Local Index）。

drop index XXX.idx_T_TEST_name;
create index XXX.i_T_TEST_name on XXX.T_TEST(name) local online;

重建后检查索引状态：

select * from dba_ind_partitions where index_name='I_T_TEST_NAME';
select * from dba_indexes where table_name='T_TEST';

6. （可选）开启行移动

如果表分区键会被更新，且更新后的值可能使行移动到其他分区，则需要开启行移动。但请注意，这可能会带来额外开销，通常不建议默认开启。

alter table XXX.T_TEST enable row movement;

总结

通过以上完整的步骤，我们成功地将一个拥有上亿记录的普通业务表，在业务几乎无感知的情况下，平滑地迁移为分区表。这不仅是数据库运维中的一项高阶技能，更是解决海量数据性能提升问题的有效实践。整个过程逻辑清晰，但每一步都需要谨慎操作，尤其是在生产环境。希望这个案例能为大家在实际工作中进行类似的数据表结构改造提供一份可靠的参考。更多深入的数据库优化技巧和实战经验，欢迎在云栈社区交流探讨。