时间戳

首页 > 解决方案 > 存储过程在从源视图更新表时存在性能问题

oracle - 存储过程在从源视图更新表时存在性能问题

问题描述

Source_View 只有近 800 条记录，但我的过程需要将近 3 分钟来更新 PHONE 表。我在每一步都有评论来解释逻辑。任何帮助表示赞赏。

要求：创建一个流程以使用视图每天检查电话号码并相应地更新它。

1) 从表 1 SELCT * FROM Table1 WHERE CATEGORY = 'T' 中获取所有区域 keycol；

2) 从 table2 获取活跃的销售代表数据并获取他的用户 ID SELECT USER_ID FROM Table2 WHERE key_colval = '' AND JOB_TITLE = 'Sales Rep' AND STATUS = 'Active';

3) 使用该用户 ID 查询 source_VIEW SELECT * FROM source_VIEW WHERE USER_ID = '' AND key_colval = '';

4) 如果我们在上述步骤中没有找到任何东西，那么 4.1) SELECT * FROM source_VIEW WHERE key_colval = '';

PROCEDURE main_PHONE_UPD 

IS

   V_USER_ID                      VARCHAR2(10);
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP_source_VIEW';

    EXECUTE IMMEDIATE 'INSERT INTO TMP_source_VIEW  SELECT SUBSTR(key_col,6) key_colval,MOBILE,USER_ID FROM  source_VIEW WHERE MOBILE IS NOT NULL'; -- Loading to temp table from source view 
    COMMIT;

   FOR REC IN (SELECT key_colval
                 FROM Table1
                WHERE CATEGORY = 'T' ) LOOP --- Getting only category 'T' from Table 1

      BEGIN
         SELECT USER_ID         -- Get user_id for given keycolval , getting only one value based on hire_date
           INTO V_USER_ID
           FROM(SELECT USER_ID
                  FROM Table2
                WHERE key_colval   = REC.key_colval
                  AND JOB_TITLE          = 'Sales Rep' 
                  AND STATUS = 'Active'
                  ORDER BY HIRE_DATE ASC)
          WHERE ROWNUM <= 1;          
         calling_PHONE_upd(V_USER_ID, REC.key_colval,'Y'); -- if data exists call this with indicator 'Y' to load phone table 
         COMMIT; 
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            calling_PHONE_upd(NULL, REC.key_colval,'N');  -- -- if data exists call this with indicator 'N' to load phone table 
            COMMIT;
      END;

   END LOOP;
END main_PHONE_UPD;


PROCEDURE calling_PHONE_upd( 

   IN_USER_ID                    VARCHAR2,
   IN_key_colval                 VARCHAR2,
   IN_USER_INDICATOR             VARCHAR2)
IS
    V_COUNT                 NUMBER := 0;
    V_PHONE_REC             PHONE%ROWTYPE;

BEGIN


   IF IN_USER_INDICATOR = 'Y' THEN
   FOR rec IN (SELECT * FROM TMP_user_VIEW WHERE USER_ID=IN_USER_ID AND key_colval=IN_key_colval )
   LOOP  - This logic to update primary/secondary numbers if mutilple values 
      V_COUNT := V_COUNT + 1;
      V_PHONE_REC.key_colval  := rec.key_colval;
      V_PHONE_REC.PHONE_NUMBER_TYPE := CASE WHEN V_COUNT = 1 THEN 'PRI'
                                            WHEN V_COUNT = 2 THEN 'SCD'
                                            ELSE NULL
                                       END;
      V_PHONE_REC.PHONE_AREA_CODE   := SUBSTR(rec.MOBILE,1,3);
      V_PHONE_REC.PHONE_NUMBER      := SUBSTR(rec.MOBILE,5,3)||SUBSTR(rec.MOBILE,9);
      V_PHONE_REC.PHONE_EXTENSION   := NULL;
      BEGIN 
            INSERT INTO PHONE VALUES V_PHONE_REC;
      EXCEPTION
          WHEN DUP_VAL_ON_INDEX THEN
              BEGIN
                  UPDATE PHONE SET ROW = V_PHONE_REC WHERE key_colval = V_PHONE_REC.key_colval AND PHONE_NUMBER_TYPE = V_PHONE_REC.PHONE_NUMBER_TYPE;
      END;
      IF V_COUNT > 2 THEN --Primary Phone Number
          EXIT;
      END IF;
   END LOOP;

   ELSE

   FOR rec IN (SELECT * FROM TMP_user_VIEW WHERE key_colval=IN_key_colval)
   LOOP  -- - This logic to update primary/secondary numbers if mutilple values 
      V_COUNT := V_COUNT + 1;
      V_PHONE_REC.PHONE_NUMBER_TYPE := CASE WHEN V_COUNT = 1 THEN 'PRI'
                                            WHEN V_COUNT = 2 THEN 'SCD'
                                            ELSE NULL
                                       END;
      V_PHONE_REC.PHONE_AREA_CODE   := SUBSTR(rec.MOBILE,1,3);
      V_PHONE_REC.PHONE_NUMBER      := SUBSTR(rec.MOBILE,5,3)||SUBSTR(rec.MOBILE,9);
      V_PHONE_REC.PHONE_EXTENSION   := NULL;
      BEGIN 
            INSERT INTO PHONE VALUES V_PHONE_REC;
      EXCEPTION
          WHEN DUP_VAL_ON_INDEX THEN
              BEGIN
                  UPDATE PHONE SET ROW = V_PHONE_REC WHERE key_colval = V_PHONE_REC.key_colval AND PHONE_NUMBER_TYPE = V_PHONE_REC.PHONE_NUMBER_TYPE;
      END;
      IF V_COUNT > 2 THEN --Primary Phone Number
          EXIT;
      END IF;
   END LOOP;

   END IF;

END calling_PHONE_upd;

标签： oracleperformanceplsql

解决方案

如果我是你，我希望把它变成一个单一的MERGE陈述，类似于：

MERGE INTO phone tgt
USING (SELECT t1.key_colval,
              t2.user_id,
              CASE WHEN t2.user_id IS NULL THEN 'N' ELSE 'Y' END INDICATOR
              CASE WHEN row_number() OVER (PARTITION BY t1.colval ORDER BY t1.colval) = 1 THEN 'PRI'
                   ELSE 'SCD'
              END phone_number_type,
              SUBSTR(tuv.MOBILE,1,3) PHONE_AREA_CODE,
              SUBSTR(tuv.MOBILE,5,3)||SUBSTR(tuv.MOBILE,9) PHONE_NUMBER,
              NULL PHONE_EXTENSION
       FROM   Table1 t1
              LEFT OUTER JOIN (SELECT key_colval,
                                      user_id
                               FROM   (SELECT user_id,
                                              key_colval,
                                              row_number() OVER (PARTITION BY key_colval ORDER BY hire_date DESC) rn
                                       FROM   table2
                                       WHERE  job_title = 'Sales Rep'
                                       AND    status = 'Active') t
                               WHERE  rn = 1) t2 ON t1.key_colval = t2.key_colval
              INNER JOIN tmp_user_view tuv ON tuv.key_colval = t1.key_colval AND ((t2.user_id IS NOT NULL AND tuv.user_id = t2.user_id) OR t2.user_id IS NULL)
       WHERE  t1.CATEGORY = 'T'
       AND    ROWNUM <= 2 -- there's no ordering mentioned in your calling_PHONE_upd cursors, so if there should be, you'd need another method of working out the correct 2 rows to return and in which order
       ) src
       ON (tgt.key_colval = src.key_colval -- plus additional columns that make the join between the tgt table and src subquery produce a 1-2-1 mapping
           )
WHEN MATCHED THEN
  UPDATE SET tgt.phone_number_type = src.phone_number_type -- add in the other columns (not the ones in the ON clause above!)
WHEN NOT MATCHED THEN
  INSERT (tgt.key_colval, ...) -- list the other columns being inserted into
  VALUES (src.key_colval, ...); -- list the other source columns being inserted

在通过您的逻辑并将手动嵌套循环连接（游标循环中的游标循环）转换为单个选择语句后，我提出了此语句，然后使用它进行合并，而不是插入或更新语句。

这应该会极大地改善事情，因为您现在可以让优化器确定连接表的最佳方式，并且您正在切断 SQL 和 PL/SQL 之间的所有上下文切换。

我也不会为临时表烦恼；只需使用直接在 Merge 语句的源子查询中填充临时表的查询。这可以节省您截断和插入数据的时间。

注意我的陈述显然未经测试，因为您没有提供包含样本数据和预期输出的完整测试用例。如果它没有按您的预期工作，我建议您针对您的情况修复它，而不是继续使用您目前拥有的高度程序化的代码。

推荐阅读