Oracle 大表加字段：UPDATE vs CREATE 性能大 PK

作者： duliang / 2024年12月16日

大表加字段是 DBA 最常见的“心惊肉跳”操作之一：

直接 ALTER TABLE … ADD 会把新列置于行尾，如果同时设为 NOT NULL，Oracle 需要立即给 每一行 填充值，产生大量 行迁移/行扩展。
为了降低停机时间，业界通常有两条路：
1. 原地更新（UPDATE）：先加 nullable 列，再 UPDATE … SET，最后改 NOT NULL。
2. 整表重建（CREATE）：建新表 → CTAS → 在线切换。

下面用 10 万行测试数据跑一遍，看看谁更快、谁更稳。

环境

参数	值
表名	T_TEST
行数	100 000
初始化字段	C_PK (PK), C_LIST, C_DATE, C_VARCHAR
新增字段	C_VARCHAR_NN VARCHAR2(30) NOT NULL
初始值	NVL(C_VARCHAR, ‘-1’)
索引	3 个普通索引 + 1 个主键

方案 1：原地 UPDATE

步骤脚本

-- 计时开始
SELECT DBMS_UTILITY.get_time FROM dual;

-- 1. 先加 nullable 列
ALTER TABLE t_test ADD c_varchar_nn VARCHAR2(30);

-- 2. 批量填充
UPDATE t_test
   SET c_varchar_nn = NVL(c_varchar, '-1');
COMMIT;

-- 3. 改 NOT NULL
ALTER TABLE t_test MODIFY c_varchar_nn VARCHAR2(30) NOT NULL;

-- 4. 整理碎片 + 重建索引
ALTER TABLE t_test MOVE;
ALTER INDEX sys_c0025206 REBUILD;  -- PK
ALTER INDEX idx_t_test_1  REBUILD;
ALTER INDEX idx_t_test_2  REBUILD;
ALTER INDEX idx_t_test_3  REBUILD;

-- 5. 新索引
CREATE INDEX idx_t_test_4 ON t_test(c_varchar_nn);

-- 6. 统计信息
ANALYZE TABLE t_test COMPUTE STATISTICS;

-- 计时结束
SELECT DBMS_UTILITY.get_time FROM dual;

结果

阶段	耗时
总耗时	10.08 s
更新行数	100 000
重建索引	4 个

方案 2：整表重建（CTAS）

步骤脚本

-- 计时开始
SELECT DBMS_UTILITY.get_time FROM dual;

-- 1. CTAS 并行建表
CREATE TABLE t_test_1
(
  c_pk         PRIMARY KEY,
  c_list       NOT NULL,
  c_date       NOT NULL,
  c_varchar,
  c_varchar_nn NOT NULL
)
NOLOGGING
PARALLEL 2
AS
SELECT c_pk,
       c_list,
       c_date,
       c_varchar,
       NVL(c_varchar, '-1') AS c_varchar_nn
  FROM t_test;

-- 2. 切换表名
DROP TABLE t_test PURGE;
ALTER TABLE t_test_1 RENAME TO t_test;

-- 3. 并行创建索引，再改回 LOGGING/NOPARALLEL
CREATE INDEX idx_t_test_1 ON t_test(c_list)   NOLOGGING PARALLEL 2;
ALTER INDEX idx_t_test_1 LOGGING NOPARALLEL;

CREATE INDEX idx_t_test_2 ON t_test(c_date)   NOLOGGING PARALLEL 2;
ALTER INDEX idx_t_test_2 LOGGING NOPARALLEL;

CREATE INDEX idx_t_test_3 ON t_test(c_varchar) NOLOGGING PARALLEL 2;
ALTER INDEX idx_t_test_3 LOGGING NOPARALLEL;

CREATE INDEX idx_t_test_4 ON t_test(c_varchar_nn) NOLOGGING PARALLEL 2;
ALTER INDEX idx_t_test_4 LOGGING NOPARALLEL;

-- 4. 统计信息 + 日志属性
ANALYZE TABLE t_test COMPUTE STATISTICS;
ALTER TABLE t_test LOGGING NOPARALLEL;

-- 计时结束
SELECT DBMS_UTILITY.get_time FROM dual;

结果

阶段	耗时
总耗时	6.36 s
并行度	2
日志模式	NOLOGGING（仅测试环境，生产需评估）

性能对比

维度	UPDATE 方案	CREATE 方案
耗时	10.08 s	6.36 s ↓37%
REDO	大量（UPDATE+MOVE+REBUILD）	极少（NOLOGGING）
UNDO	高	低
并行	无	有
锁表时间	长（UPDATE+MOVE）	短（仅切换瞬间）
步骤复杂度	低	高（需要改名、重建约束/索引）

最佳实践总结

场景	推荐方案
停机窗口 < 10 分钟	CREATE+在线重命名（本文方案 2）
需要闪回/可回滚	UPDATE，或 CREATE 后保留原表
生产库开启 FORCE LOGGING	CTAS 阶段需改为 `NOLOGGING` → `LOGGING`
超大表（亿级）	分片并行 CTAS + 交换分区
有外键依赖	重建后别忘了重新创建 FK，或改用 DBMS_REDEFINITION

10 万行差距 4 秒，1000 万行差距可能就是 小时级。记住口诀：小表直接改，大表最好建！

发表评论取消回复