如何优化含13万+记录的UPDATE语句执行效率？

问题背景 #

需要更新包含137459条记录的TF_IMPORT_BILL_ISSUE表，将符合条件的记录的OPERATION_ID字段设置为对应BILL_ID在TF_IMP_BILL_TRANSACTIONS中的最大TRANSACTION_ID。原查询成本显示为4，但始终无法正常执行。

表记录数统计 #

• TF_IMPORT_BILL_ISSUE：137,459条
• TF_IMP_BILL_TRANSACTIONS：239,712条
• CONV_BRANCH_INFO：2条

原查询语句 #

UPDATE TF_IMPORT_BILL_ISSUE N
   SET N.OPERATION_ID =
          (SELECT TRANSACTION_ID
             FROM (  SELECT /*+ INDEX(BT TF_IMP_BILL_TXN_BILLID) */
                           MAX (BT.TRANSACTION_ID) TRANSACTION_ID, BI.BILL_ID
                       FROM TF_IMP_BILL_TRANSACTIONS BT,
                            TF_IMPORT_BILL_ISSUE BI,
                            CONV_BRANCH_INFO BRANCH
                      WHERE     BRANCH.IS_FOR_MIGRATION = 1
                            AND BI.OPERATION_ID IS NULL
                            AND BRANCH.BRANCH_ID = BI.OWNER_BRANCH_ID
                            AND BT.BILL_ID = BI.BILL_ID
                   GROUP BY BI.BILL_ID) v
            WHERE v.bill_id = n.bill_id)
 WHERE N.BILL_ID IN (SELECT bill_id
                       FROM (  SELECT /*+ INDEX(BI) */
                                     MAX (BT.TRANSACTION_ID) TRANSACTION_ID,
                                      BI.BILL_ID
                                 FROM TF_IMP_BILL_TRANSACTIONS BT,
                                      TF_IMPORT_BILL_ISSUE BI,
                                      CONV_BRANCH_INFO BRANCH
                                WHERE     BRANCH.IS_FOR_MIGRATION = 1
                                      AND BI.OPERATION_ID IS NULL
                                      AND BRANCH.BRANCH_ID = BI.OWNER_BRANCH_ID
                                      AND BT.BILL_ID = BI.BILL_ID
                             GROUP BY BI.BILL_ID) V);

原执行计划 #

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                           |                           |     1 |    52 |     4  (25)| 00:00:01 |
|   1 |  UPDATE                                    | TF_IMPORT_BILL_ISSUE      |       |       |            |          |
|   2 |   NESTED LOOPS                             |                           |     1 |    52 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                            |                           |     1 |    52 |     1   (0)| 00:00:01 |
|   4 |     VIEW                                   | VW_NSO_1                  |     1 |    13 |     0   (0)| 00:00:01 |
|   5 |      SORT GROUP BY                         |                           |     1 |    58 |     0   (0)| 00:00:01 |
|   6 |       NESTED LOOPS SEMI                    |                           |     1 |    58 |     0   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                        |                           |     1 |    52 |     0   (0)| 00:00:01 |
|   8 |         INDEX FULL SCAN                    | TF_IMP_BILL_TXN_BILLID    |     1 |    13 |     0   (0)| 00:00:01 |
|*  9 |         TABLE ACCESS BY INDEX ROWID BATCHED| TF_IMPORT_BILL_ISSUE      |     1 |    39 |     0   (0)| 00:00:01 |
|  10 |          INDEX FULL SCAN                   | TF_IMPORT_BILL_ISSUE_ONBR |     1 |       |     0   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS BY INDEX ROWID BATCHED | CONV_BRANCH_INFO          |     2 |    12 |     0   (0)| 00:00:01 |
|* 12 |         INDEX RANGE SCAN                   | CBI_BR_ID                 |     1 |       |     0   (0)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN                      | SYS_C0016767              |     1 |       |     0   (0)| 00:00:01 |
|  14 |    TABLE ACCESS BY INDEX ROWID             | TF_IMPORT_BILL_ISSUE      |     1 |    39 |     1   (0)| 00:00:01 |
|  15 |   VIEW                                     |                           |     1 |    26 |     1   (0)| 00:00:01 |
|  16 |    SORT GROUP BY                           |                           |     1 |    71 |     1   (0)| 00:00:01 |
|  17 |     NESTED LOOPS                           |                           |     1 |    71 |     1   (0)| 00:00:01 |
|  18 |      NESTED LOOPS                          |                           |     1 |    45 |     1   (0)| 00:00:01 |
|* 19 |       TABLE ACCESS BY INDEX ROWID          | TF_IMPORT_BILL_ISSUE      |     1 |    39 |     1   (0)| 00:00:01 |
|* 20 |        INDEX UNIQUE SCAN                   | SYS_C0016767              |     1 |       |     1   (0)| 00:00:01 |
|* 21 |       TABLE ACCESS BY INDEX ROWID BATCHED  | CONV_BRANCH_INFO          |     1 |     6 |     0   (0)| 00:00:01 |
|* 22 |        INDEX RANGE SCAN                    | CBI_BR_ID                 |     1 |       |     0   (0)| 00:00:01 |
|  23 |      TABLE ACCESS BY INDEX ROWID BATCHED   | TF_IMP_BILL_TRANSACTIONS  |     1 |    26 |     0   (0)| 00:00:01 |
|* 24 |       INDEX RANGE SCAN                     | TF_IMP_BILL_TXN_BILLID    |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - filter("BI"."OPERATION_ID" IS NULL AND "BT"."BILL_ID"="BI"."BILL_ID")
  11 - filter("BRANCH"."IS_FOR_MIGRATION"=1)
  12 - access("BRANCH"."BRANCH_ID"="BI"."OWNER_BRANCH_ID")
  13 - access("N"."BILL_ID"="BILL_ID")
  19 - filter("BI"."OPERATION_ID" IS NULL)
  20 - access("BI"."BILL_ID"=:B1)
  21 - filter("BRANCH"."IS_FOR_MIGRATION"=1)
  22 - access("BRANCH"."BRANCH_ID"="BI"."OWNER_BRANCH_ID")
  24 - access("BT"."BILL_ID"=:B1)

优化方案 #

1. 核心优化：消除重复计算，改用MERGE语句 #

原查询的SET子句和WHERE子句重复执行了完全相同的聚合逻辑，导致双倍计算开销。改用MERGE语句可以只执行一次聚合查询，同时让优化器选择更高效的连接策略。

优化后的查询：

MERGE INTO TF_IMPORT_BILL_ISSUE N
USING (
    SELECT 
        BI.BILL_ID,
        MAX(BT.TRANSACTION_ID) AS TRANSACTION_ID
    FROM TF_IMPORT_BILL_ISSUE BI
    JOIN CONV_BRANCH_INFO BRANCH 
        ON BRANCH.BRANCH_ID = BI.OWNER_BRANCH_ID
        AND BRANCH.IS_FOR_MIGRATION = 1
    JOIN TF_IMP_BILL_TRANSACTIONS BT 
        ON BT.BILL_ID = BI.BILL_ID
    WHERE BI.OPERATION_ID IS NULL
    GROUP BY BI.BILL_ID
) V
ON (N.BILL_ID = V.BILL_ID)
WHEN MATCHED THEN
    UPDATE SET N.OPERATION_ID = V.TRANSACTION_ID;

2. 优化点说明 #

• 避免重复计算：仅执行一次聚合子查询，大幅降低CPU和IO开销
• 高效连接策略：优化器会自动选择以CONV_BRANCH_INFO（仅2条记录）为驱动表，依次关联TF_IMPORT_BILL_ISSUE和TF_IMP_BILL_TRANSACTIONS，避免嵌套循环的低效遍历
• 移除强制索引提示：让优化器根据最新统计信息自动选择最优索引（现有TF_IMP_BILL_TXN_BILLID和TF_IMPORT_BILL_ISSUE_ONBR索引已足够支持查询）

3. 额外建议 #

（1）更新表统计信息

确保优化器能获取准确的行数估算，执行：

DBMS_STATS.GATHER_TABLE_STATS('你的Schema名', 'TF_IMPORT_BILL_ISSUE');
DBMS_STATS.GATHER_TABLE_STATS('你的Schema名', 'TF_IMP_BILL_TRANSACTIONS');

（2）分批更新（若更新量较大）

如果需要更新的记录数过多，可使用分批更新避免锁表或事务日志溢出：

DECLARE
    CURSOR c_update IS
        SELECT 
            BI.BILL_ID,
            MAX(BT.TRANSACTION_ID) AS TRANSACTION_ID
        FROM TF_IMPORT_BILL_ISSUE BI
        JOIN CONV_BRANCH_INFO BRANCH 
            ON BRANCH.BRANCH_ID = BI.OWNER_BRANCH_ID
            AND BRANCH.IS_FOR_MIGRATION = 1
        JOIN TF_IMP_BILL_TRANSACTIONS BT 
            ON BT.BILL_ID = BI.BILL_ID
        WHERE BI.OPERATION_ID IS NULL
        GROUP BY BI.BILL_ID;
    TYPE t_update IS TABLE OF c_update%ROWTYPE;
    l_batch t_update;
BEGIN
    OPEN c_update;
    LOOP
        FETCH c_update BULK COLLECT INTO l_batch LIMIT 1000; -- 每次批量更新1000条
        EXIT WHEN l_batch.COUNT = 0;
        
        FORALL i IN 1..l_batch.COUNT
            UPDATE TF_IMPORT_BILL_ISSUE N
            SET N.OPERATION_ID = l_batch(i).TRANSACTION_ID
            WHERE N.BILL_ID = l_batch(i).BILL_ID;
            
        COMMIT;
    END LOOP;
    CLOSE c_update;
END;
/

内容的提问来源于stack exchange，提问作者Sadman ZIhan