在千萬級以上數據更新場景中，UPDATE往往比INSERT慢3-5倍，主因在於：
buffer busy waits本文提供從SQL改寫、會話參數、並行執行到表設計的全鏈路優化方案。
-- 更新非索引列：無需動索引
-- 更新索引列：權衡利弊
ALTER INDEX idx_col UNUSABLE; -- 禁用索引
UPDATE large_table SET indexed_col = ...;
ALTER INDEX idx_col REBUILD; -- 重建索引（可並行、NOLOGGING）決策矩陣：
| 場景 | 建議 |
|---|---|
| 更新列無索引 | 直接執行 |
| 更新列有索引，更新量>20%表行數 | 禁用→更新→重建 |
| 更新列有索引，更新量<5% | 保留索引（利用索引定位加速） |
DECLARE
v_cnt NUMBER := 0;
BEGIN
FOR r IN (SELECT rowid, col FROM large_table WHERE condition) LOOP
UPDATE large_table SET col = new_val WHERE rowid = r.rowid;
v_cnt := v_cnt + 1;
IF MOD(v_cnt, 5000) = 0 THEN -- 每5000行提交
COMMIT;
END IF;
END LOOP;
COMMIT; -- 尾批提交
END;關鍵參數：提交頻率=Undo保留需求/Undo表空間大小，典型值1000-10000行/批。
CREATE TEMPORARY TABLESPACE dml_temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10G AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER USER etl_user TEMPORARY TABLESPACE dml_temp;防止大批量DML排序溢寫佔用默認TEMP，影響並發查詢。
ALTER SESSION SET sort_area_size = 100000000; -- 100MB
-- 10g+建議改用PGA_AGGREGATE_TARGET自動管理
-- 或會話級：ALTER SESSION SET workarea_size_policy = MANUAL;減少磁盤排序，適用於INSERT ... SELECT、CREATE TABLE AS SELECT等大數據集操作。
當更新行數接近表總行數的15-20%時，優化器傾向全表掃描。強制走索引反而拖垮性能。
-- 強制全表掃描（適合大比例更新）
UPDATE /*+ FULL(t) */ large_table t SET ... WHERE ...;
-- 強制索引掃描（適合小比例精準更新）
UPDATE /*+ INDEX(t idx_col) */ large_table t SET ... WHERE ...;每個索引都是更新負擔。定期運行DBA_UNUSED_INDEXES檢測，刪除：
-- 會話啟用PDML
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
-- 或語句級Hint
UPDATE /*+ PARALLEL(t, 4) */ large_table t SET col = ... WHERE ...;前置條件：
PARALLEL屬性或Hint指定度-- 高頻更新表建議
CREATE TABLE hot_table (
...
) PCTFREE 20 PCTUSED 40; -- 預留20%塊空間供更新膨脹監控指標：CHAINED_ROWS / TABLE_FETCH_CONTINUED_ROWS，若增長則需調大PCTFREE或ALTER TABLE ... MOVE重組。
| 模式 | 語法示例 | Redo生成 | 速度 | 適用場景 |
|---|---|---|---|---|
| 常規INSERT | INSERT INTO t1 SELECT * FROM t; | 全量 | 基準 | 小數據量、需觸發器 |
| NOLOGGING INSERT | ALTER TABLE t1 NOLOGGING; INSERT ... | 極少（僅元數據） | 3-5x | 數據倉庫載入、可重跑 |
| CTAS (Create Table As Select) | CREATE TABLE t1 NOLOGGING PARALLEL 4 AS SELECT * FROM t; | 極少 + 並行 | 5-10x | 全表複製、重建表、分區交換前載入 |
CREATE TABLE target_table
NOLOGGING
PARALLEL (DEGREE 8)
TABLESPACE etl_data
AS
SELECT /*+ PARALLEL(s, 8) */ *
FROM source_table s
WHERE load_date = TRUNC(SYSDATE);SET ARRAYSIZE 5000
SET COPYCOMMIT 10000
COPY FROM user/pwd@src_db
APPEND target_table
USING SELECT * FROM source_table WHERE ...;參數調優：ARRAYSIZE控制網絡往返行數（建議1000-5000），COPYCOMMIT控制提交頻率。
DBMS_STATS.GATHER_TABLE_STATS）PCTFREE是否≥20%NOLOGGING+PARALLELSORT_AREA_SIZE/PGA是否適配Oracle DML優化無銀彈，核心在於「減少Redo/Undo生成、利用並行硬件、消除塊爭用」三大物理層面。參數調優（如SORT_AREA_SIZE）僅能帶來10-15%提升，SQL重寫與架構設計決定80%性能上限。建立標準化的ETL載入模板（CTAS+NOLOGGING+PARALLEL）、更新模板（批量提交+動態索引管理），將優化經驗固化為團隊規範，才是長久之計。
