Oracle中3600万条数据的MERGE语句执行过慢问题求助

问题背景 #

我有一张包含3600万条记录的account表，需要按特定条件更新字段，但每条MERGE查询耗时10分钟，且还有大量同类查询待执行。

示例MERGE查询：

MERGE INTO account USING ( 
    SELECT id, COUNT(Account) as numAcc
    FROM account  
    WHERE Status = 'ACTIVATED' 
    GROUP BY id
) ba 
ON (ba.id = account.id) 
WHEN MATCHED THEN UPDATE SET account.numAcc = ba.numAcc;

注：其他同类查询结构一致，仅过滤条件不同。

执行计划分析 #

当前查询的执行计划如下：

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT          |                                |    36M|   892M|       |   610K  (1)| 00:00:24 |
|   1 |  MERGE                   | ACCOUNT |       |       |       |            |          |
|   2 |   VIEW                   |                                |       |       |       |            |          |
|*  3 |    HASH JOIN             |                                |    36M|    19G|   511M|   610K  (1)| 00:00:24 |
|   4 |     VIEW                 |                                |   998K|   500M|       |   365K  (1)| 00:00:15 |
|   5 |      SORT GROUP BY       |                                |   998K|   502M|       |   365K  (1)| 00:00:15 |
|   6 |       VIEW               | VW_DAG_1                       |    25M|    12G|       |   365K  (1)| 00:00:15 |
|   7 |        SORT GROUP BY     |                                |    25M|  1025M|  1346M|   365K  (1)| 00:00:15 |
|*  8 |         TABLE ACCESS FULL| ACCOUNT |    25M|  1025M|       | 91408   (1)| 00:00:04 |
|   9 |     TABLE ACCESS FULL    | ACCOUNT |    36M|  2162M|       | 91501   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("BA"."ID"="ACCOUNT"."ID")
   8 - filter("STATUS"='ACTIVATED')

从执行计划能看到几个关键性能问题：

• 两次全表扫描（操作8和9），重复读取3600万条数据，IO开销极大
• 两次SORT GROUP BY（操作5和7），需要大量临时空间排序，CPU和内存消耗高
• 执行计划估算耗时24秒，但实际跑10分钟，说明表的统计信息过期，优化器选了低效执行路径

样本数据 #

id           account     status month
3141243131  1162278310  CLOSED  1
3141243131  1014532510  ACTIVATED   1
3141243131  1094289210  ACTIVATED   1
3141243131  1162278310  CLOSED  2
3141243131  1094289210  ACTIVATED   2
3141243131  1014532510  ACTIVATED   2
3141243131  1094289210  ACTIVATED   3
3141243131  1162278310  CLOSED  3
3141243131  1014532510  ACTIVATED   3
5432523522  1111231231  CLOSED  1
5432523522  1014532510  ACTIVATED   1
5432523522  1094589210  ACTIVATED   1
5432523522  1111231231  CLOSED  2
5432523522  1094289210  ACTIVATED   2
5432523522  1094289210  ACTIVATED   2
5432523522  1094289210  CLOSED  3
5432523522  1111231231  CLOSED  3
5432523522  1094589210  ACTIVATED   3

优化方案 #

1. 替换MERGE为单扫描的UPDATE语句 #

用子查询或临时表减少全表扫描次数：

直接关联更新

UPDATE account
SET numAcc = (
    SELECT COUNT(account)
    FROM account a
    WHERE a.id = account.id AND a.Status = 'ACTIVATED'
)
WHERE EXISTS (
    SELECT 1
    FROM account a
    WHERE a.id = account.id AND a.Status = 'ACTIVATED'
);

临时表预计算（更适合大量同类查询）

-- 创建临时表存聚合结果
CREATE GLOBAL TEMPORARY TABLE temp_acc_stats (
    id NUMBER,
    numAcc NUMBER
) ON COMMIT PRESERVE ROWS;

INSERT INTO temp_acc_stats
SELECT id, COUNT(Account) as numAcc
FROM account
WHERE Status = 'ACTIVATED'
GROUP BY id;

-- 关联临时表更新，避免重复扫原表
UPDATE account b
SET numAcc = (SELECT numAcc FROM temp_acc_stats a WHERE a.id = b.id)
WHERE EXISTS (SELECT 1 FROM temp_acc_stats a WHERE a.id = b.id);

-- 清理临时表
TRUNCATE TABLE temp_acc_stats;

2. 添加组合索引消除全表扫描和排序 #

创建针对过滤条件+分组字段的组合索引，让优化器快速筛选数据并完成分组，不用全表扫和排序：

CREATE INDEX idx_account_status_id ON account(Status, id);

这个索引能把操作8的全表扫描改成索引范围扫描，同时GROUP BY id时可以利用索引的有序性，直接跳过SORT GROUP BY步骤，大幅降IO和CPU开销。

3. 更新表统计信息 #

执行计划估算和实际耗时差太多，说明统计信息过期，重新收集：

-- Oracle环境下执行
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'ACCOUNT', CASCADE => TRUE);

更新后优化器会生成更准确的执行计划，选更高效的路径。

4. 批量合并同类查询 #

如果有大量仅条件不同的查询，把多个条件合并成一个，一次扫表完成多个字段更新：

MERGE INTO account USING (
    SELECT 
        id,
        COUNT(CASE WHEN Status = 'ACTIVATED' THEN Account END) as activated_num,
        COUNT(CASE WHEN Status = 'CLOSED' THEN Account END) as closed_num
    FROM account
    GROUP BY id
) ba
ON (ba.id = account.id)
WHEN MATCHED THEN UPDATE SET 
    account.activated_num = ba.activated_num,
    account.closed_num = ba.closed_num;

这样一次扫表就能搞定多个字段更新，避免重复扫表。

内容的提问来源于stack exchange，提问作者MIX 2000