Oracle PL/SQL动态查询长运行问题优化请求

以下PL/SQL代码用于生成动态UPDATE并记录操作日志，但存在运行耗时过长的问题，针对核心性能瓶颈，优化方案如下：

原代码 #

DECLARE
TYPE MFU_REC_TYPE
IS
  RECORD
  (
    Product_Category VARCHAR2(4000),
    SOURCE_COLUMN    VARCHAR2(4000),
    SOURCE_VALUE     VARCHAR2(4000),
    TARGET_COLUMNS   VARCHAR2(4000),
    TARGET_VALUE     VARCHAR2(4000),
    TP_SYS_NM        VARCHAR2(4000) );
TYPE MFU_table_type
IS
  TABLE OF MFU_REC_TYPE INDEX BY PLS_INTEGER;
  MFU_VALUES MFU_table_type;
TYPE log_REC_TYPE
IS
  RECORD
  (
    log_type        VARCHAR2(4000),
    Update_query    VARCHAR2(4000),
    Process_Message VARCHAR2(4000) );
TYPE log_table_type
IS
  TABLE OF log_REC_TYPE;
  Log_VALUES log_table_type:=log_table_type();
  LS_SQL          VARCHAR2(1000);
  LS_MFU_TABLE    VARCHAR2(1000);
  LS_AGG_TABLE    VARCHAR2(1000);
  ls_update_query VARCHAR2(4000);
  lb_exception    BOOLEAN;
BEGIN
  lb_exception :=false;
  LS_MFU_TABLE := @MFU_Table_name ;
  LS_AGG_TABLE :=@AGG_Table_name;
  
  LS_SQL       := 'SELECT Product_Category,
SOURCE_COLUMN,    
SOURCE_VALUE,    
TARGET_COLUMNS,  
TARGET_VALUE, 
TP_SYS_NM   
FROM '||LS_MFU_TABLE;

  EXECUTE IMMEDIATE ls_sql BULK COLLECT INTO MFU_values;
  
  FOR i IN 1 .. MFU_values.COUNT
  LOOP
    BEGIN
      Log_VALUES.extend;
      
      ls_update_query           := 'update  '||LS_AGG_TABLE||'  set  ' ||MFU_values(i).TARGET_COLUMNS|| ' = '||''''||MFU_values(i).TARGET_VALUE||''''|| ' where trim(' ||MFU_values(i).SOURCE_COLUMN||') = trim( '||''''|| MFU_values(i).SOURCE_VALUE||''''|| ') and  trim(PRODUCT_CATEGORY) = trim('||''''|| MFU_values(i).Product_Category||''''|| ') ' ;
      
      Log_VALUES(i).Update_query:=ls_update_query;
      
      EXECUTE immediate ls_update_query;
      
      Log_VALUES(i).Process_Message:=SQL%ROWCOUNT||' rows got updated.';
      Log_VALUES(i).log_type       :='OK';
      
    EXCEPTION
    WHEN OTHERS THEN
      Log_VALUES(i).Process_Message:=SQLERRM;
      Log_VALUES(i).log_type       :='ERROR';
      lb_exception                 :=true;
    END;
    
  END LOOP;
  
  IF lb_exception THEN
    ROLLBACK;
  END IF;
  
  FOR i IN Log_VALUES.FIRST..Log_VALUES.LAST
  LOOP
    INSERT
    INTO @table_name
      (
        log_type,
        Update_query,
        Process_Message
      )
      VALUES
      (
        Log_VALUES(i).log_type,
        Log_VALUES(i).Update_query,
        Log_VALUES(i).Process_Message
      );
  END LOOP;
  
  COMMIT;
 
END;

核心优化点 #

1. 批量执行更新，消除单条循环开销 #

原代码对每条MFU记录单独执行UPDATE，多次硬解析和数据库交互是主要性能瓶颈。改用MERGE批量更新，将MFU表的更新规则一次性关联到AGG表，大幅减少SQL执行次数。

2. 避免WHERE子句中的TRIM函数，恢复索引有效性 #

原代码中trim(SOURCE_COLUMN)、trim(PRODUCT_CATEGORY)会导致AGG表上的对应索引无法被使用，全表扫描耗时剧增。优化方案：

• 提前清洗MFU表中的数据，确保SOURCE_VALUE和Product_Category无前后空格，去掉WHERE子句中的TRIM
• 若业务必须保留TRIM，在AGG表上创建函数索引：CREATE INDEX idx_agg_source_trim ON AGG_TABLE(trim(SOURCE_COLUMN), trim(PRODUCT_CATEGORY));

3. 批量插入日志，替代循环插入 #

原代码循环插入日志表，改用FORALL批量插入，减少SQL引擎调用次数，提升日志写入效率。

4. 使用绑定变量，减少硬解析 #

原动态SQL通过字符串拼接生成，每条SQL都是新语句，产生大量硬解析。改用绑定变量复用执行计划，降低解析开销。

5. 调整事务逻辑，灵活处理异常 #

原代码只要有一条更新失败就全量回滚，若业务允许，可改为记录异常后继续执行，避免单次回滚大量数据；若必须保证原子性，可分批次处理，缩小回滚范围。

优化后的代码示例 #

DECLARE
    TYPE MFU_REC_TYPE IS RECORD (
        Product_Category VARCHAR2(4000),
        SOURCE_COLUMN    VARCHAR2(4000),
        SOURCE_VALUE     VARCHAR2(4000),
        TARGET_COLUMNS   VARCHAR2(4000),
        TARGET_VALUE     VARCHAR2(4000),
        TP_SYS_NM        VARCHAR2(4000)
    );
    TYPE MFU_table_type IS TABLE OF MFU_REC_TYPE INDEX BY PLS_INTEGER;
    MFU_VALUES MFU_table_type;

    TYPE log_REC_TYPE IS RECORD (
        log_type        VARCHAR2(4000),
        Update_query    VARCHAR2(4000),
        Process_Message VARCHAR2(4000)
    );
    TYPE log_table_type IS TABLE OF log_REC_TYPE;
    Log_VALUES log_table_type := log_table_type();

    LS_MFU_TABLE    VARCHAR2(1000) := '@MFU_Table_name';
    LS_AGG_TABLE    VARCHAR2(1000) := '@AGG_Table_name';
    LS_LOG_TABLE    VARCHAR2(1000) := '@table_name';
    lb_exception    BOOLEAN := FALSE;
    lv_merge_sql    VARCHAR2(32767);
BEGIN
    -- 批量获取更新规则
    EXECUTE IMMEDIATE 'SELECT Product_Category, SOURCE_COLUMN, SOURCE_VALUE, TARGET_COLUMNS, TARGET_VALUE, TP_SYS_NM FROM ' || LS_MFU_TABLE
        BULK COLLECT INTO MFU_VALUES;

    -- 遍历更新规则，生成批量MERGE语句（可进一步按相同TARGET_COLUMN分组处理）
    FOR i IN 1 .. MFU_VALUES.COUNT LOOP
        BEGIN
            Log_VALUES.EXTEND;
            -- 使用绑定变量构建MERGE语句
            lv_merge_sql := 'MERGE INTO ' || LS_AGG_TABLE || ' t
                            USING (SELECT :p_source_val AS source_val, :p_prod_cat AS prod_cat, :p_target_val AS target_val FROM DUAL) s
                            ON (t.' || MFU_VALUES(i).SOURCE_COLUMN || ' = s.source_val AND t.PRODUCT_CATEGORY = s.prod_cat)
                            WHEN MATCHED THEN UPDATE SET t.' || MFU_VALUES(i).TARGET_COLUMNS || ' = s.target_val';

            Log_VALUES(i).Update_query := lv_merge_sql;
            -- 执行绑定变量的MERGE
            EXECUTE IMMEDIATE lv_merge_sql
                USING MFU_VALUES(i).SOURCE_VALUE, MFU_VALUES(i).Product_Category, MFU_VALUES(i).TARGET_VALUE;

            Log_VALUES(i).Process_Message := SQL%ROWCOUNT || ' rows got updated.';
            Log_VALUES(i).log_type := 'OK';
        EXCEPTION
            WHEN OTHERS THEN
                Log_VALUES(i).Process_Message := SQLERRM;
                Log_VALUES(i).log_type := 'ERROR';
                lb_exception := TRUE;
        END;
    END LOOP;

    -- 事务处理：根据业务需求选择回滚或继续
    IF lb_exception THEN
        ROLLBACK;
    END IF;

    -- 批量插入日志
    FORALL i IN Log_VALUES.FIRST .. Log_VALUES.LAST
        INSERT INTO LS_LOG_TABLE (log_type, Update_query, Process_Message)
        VALUES (Log_VALUES(i).log_type, Log_VALUES(i).Update_query, Log_VALUES(i).Process_Message);

    COMMIT;
END;

注：若MFU表中存在多条更新同一TARGET_COLUMN的规则，可进一步按TARGET_COLUMN分组，生成更高效的批量MERGE语句，避免多次扫描AGG表。

内容的提问来源于stack exchange，提问作者Guru G