Oracle 替换表的多次扫描以进行一系列更新。

我最近继承了一个运行需要很长时间的迁移套件，因此我正在研究各种长时间运行的SQL命令来尝试改进它。问题的一个方面是一些数据验证，也进行更新。每次验证都会扫描包含600万个项目的表，并在发现问题时更新错误消息列。问题是有15次验证，所以15次扫描表。我正在努力想一种方法把这些组合成一个数据库扫描，你能帮我吗？

我认为代码是由熟悉SQL的人编写的，但不是PL/SQL。在表周围设置一个循环，偶尔提交是最好的方法吗？而不是尝试将所有内容都塞进一个SQL语句中。

这是密码

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '0', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL("Scheme",'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE("Scheme",'|','/') || '^wrk_AR_PS_PPROCHIST - "Scheme" '  || 'V00846 Mandatory field'
 WHERE NVL("Scheme",'') is NULL OR  NVL("Scheme",'') = ''   ;
    
UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '0', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL(LEGACY_CREF_NO,'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE(LEGACY_CREF_NO,'|','/') || '^wrk_AR_PS_PPROCHIST - LEGACY_CREF_NO '  || 'V00847 Mandatory field'
 WHERE NVL(LEGACY_CREF_NO,'') is NULL OR  NVL(LEGACY_CREF_NO,'') = ''        ; 

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '2', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL(LEGACY_BASIC_NO,'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE(LEGACY_BASIC_NO,'|','/') || '^wrk_AR_PS_PPROCHIST - LEGACY_BASIC_NO '  || 'V00894 *Invalid Parent Client Record'
 WHERE NVL(LEGACY_BASIC_NO,'') not in (select LEGACY_BASIC_NO from wrk_ar_ps_pcontrol where valid = 1)    ;   

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '2', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL(LEGACY_POLICY_NO,'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE(LEGACY_POLICY_NO,'|','/') || '^wrk_AR_PS_PPROCHIST - LEGACY_POLICY_NO '  || 'V00895 *PS_PCONTROL entry doesnt exist'
 WHERE NVL(LEGACY_POLICY_NO,'') NOT IN (select LEGACY_POLICY_NO from wrk_AR_PS_PCONTROL where valid = 1)        ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '0', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL(LEGACY_Value_04,'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE(LEGACY_Value_04,'|','/') || '^wrk_AR_PS_PPROCHIST - LEGACY_Value_04 '  || 'V00948 Payment Date out of range for PM'
 WHERE TO_DATE(AD_DATE_FORMAT("The Date of Payment"),'DD/MM/YYYY') < TO_DATE(AD_DATE_FORMAT(LEGACY_Value_04),'DD/MM/YYYY') AND 1=2  ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '0', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL("Periodic Amount",'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE("Periodic Amount",'|','/') || '^wrk_AR_PS_PPROCHIST - "Periodic Amount" '  || 'V00950 Value can not be less than zero'
 WHERE "Periodic Amount" < 0 and "Element of the Payment" = 'PAY'     ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '0', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL("Cumulative Gross Pay to dat",'')
      , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE("Cumulative Gross Pay to dat",'|','/') || '^wrk_AR_PS_PPROCHIST - "Cumulative Gross Pay to dat" '  || 'V00951 Value can not be less than zero'
 WHERE "Cumulative Gross Pay to dat" < 0          ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '0', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL(LEGACY_Value_09,'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE(LEGACY_Value_09,'|','/') || '^wrk_AR_PS_PPROCHIST - LEGACY_Value_09 '  || 'V00967 TAX Point Date is before First Paid Date (PCONTROL)'
 WHERE TO_DATE(LEGACY_Value_09,'DD/MM/YYYY') > TO_DATE("Tax Point Date",'DD/MM/YYYY')           ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '2', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL(LEGACY_Value_05,'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE(LEGACY_Value_05,'|','/') || '^wrk_AR_PS_PPROCHIST - LEGACY_Value_05 '  || 'V01014 * Reveresal Filter for Suspended Transactions'
 WHERE ROW_UID IN (SELECT FILTER_REV FROM TMP_VW_AR_PS_PPROCHIST_SUS_FILT)    ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '0', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL("Source of Payment",'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE("Source of Payment",'|','/') || '^wrk_AR_PS_PPROCHIST - "Source of Payment" '  || 'V01016 Mandatory field'
 WHERE NVL("Source of Payment",'') is NULL OR  NVL("Source of Payment",'') = ''           ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '0', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL("Element of the Payment",'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE("Element of the Payment",'|','/') || '^wrk_AR_PS_PPROCHIST - "Element of the Payment" '  || 'V01017 Mandatory field'
 WHERE NVL("Element of the Payment",'') is NULL OR  NVL("Element of the Payment",'') = ''           ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '2', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL("Status of the Payment",'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE("Status of the Payment",'|','/') || '^wrk_AR_PS_PPROCHIST - "Status of the Payment" '  || 'V01018 *Filter out SUSPENDED transactions for CEASED members'
 WHERE NVL("Status of the Payment",'') = 'SUSPEND' AND LEGACY_BASIC_NO IN (SELECT LEGACY_BASIC_NO FROM WRK_AR_PS_PCONTROL WHERE "Status of Member" = 'CEASED')        ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '2', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL(LEGACY_Value_06,'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE(LEGACY_Value_06,'|','/') || '^wrk_AR_PS_PPROCHIST - LEGACY_Value_06 '  || 'V01019 * PayPrep Transaction'
 WHERE NVL(LEGACY_Value_06,'') in ('CASH', 'CASH(FROM SOURCE)') AND 1=2           ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '2', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL(LEGACY_Value_05,'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE(LEGACY_Value_05,'|','/') || '^wrk_AR_PS_PPROCHIST - LEGACY_Value_05 '  || 'V01025 * Filter for Reversal Transactions'
 WHERE ROW_UID IN (SELECT FILTER_REV FROM TMP_VW_AR_PS_PPROCHIST_REV_FILT) ;

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = '2', ErrorMessage = NVL(ErrorMessage,'') || '|'  || NVL(LEGACY_Value_06,'')
     , ErrorMessage_Summary = NVL(ErrorMessage_Summary,'') || '|' || REPLACE(LEGACY_Value_06,'|','/') || '^wrk_AR_PS_PPROCHIST - LEGACY_Value_06 '  || 'V01026 * Filter for Suspended Transactions'
 WHERE ROW_UID IN (SELECT FILTER_REV FROM TMP_VW_AR_PS_PPROCHIST_SUS_FILT)      ;
复制

此表开始时为空，并在运行填充的统计信息以帮助索引后进行填充

dbms_stats.gather_table_stats(ownname => 'AVIDEV', tabname =>'WRK_AR_PS_PPROCHIST', cascade => TRUE, method_opt => 'FOR ALL INDEXED COLUMNS'); 
COMMIT ;
复制

当前有4个索引
行_uid
Legacy_value_06
传统基本_否
传统基本_否, legacy_value_07

您可以将所有更改链接到一个文件中，而不是单独编写UPDATE文件。

通过在SET子句中串联两个表达式来完成此操作，例如：

UPDATE wrk_AR_PS_PPROCHIST               
   SET Valid = ...
     , ErrorMessage = 
         ErrorMessage || 
         CASE WHEN "Scheme" IS NULL 
         THEN "Scheme"
         END ||
         CASE WHEN LEGACY_CREF_NO IS NULL 
         THEN LEGACY_CREF_NO
         END ||
         ... etc. 
     , ErrorMessage_Summary = 
         ErrorMessage_Summary ||
         CASE WHEN "Scheme" IS NULL 
         THEN  '^wrk_AR_PS_PPROCHIST - "Scheme" '  || 'V00846 Mandatory field'
         END ||
         CASE WHEN LEGACY_CREF_NO IS NULL 
         THEN '^wrk_AR_PS_PPROCHIST - LEGACY_CREF_NO '  || 'V00847 Mandatory field'
         END ||
         CASE WHEN ... etc.;
复制

我不确定设置“有效”列的逻辑是什么。

如果您希望验证失败的行相对较少（即。更新只更改几行） ，将其向下筛选为仅有错误行可能会更快，因此您只更改这些行。您可以使用相关的UPDATE或合并来完成此操作，例如：

merge into wrk_ar_ps_pprochist
using (
  select * from ( 
    select id,
           ErrorMessage || 
           CASE WHEN "Scheme" IS NULL 
           THEN "Scheme"
           END ||
           CASE WHEN LEGACY_CREF_NO IS NULL 
           THEN LEGACY_CREF_NO
           END ||
           ... etc.  
             as error_details
           ErrorMessage_Summary ||
           CASE WHEN "Scheme" IS NULL 
           THEN  '^wrk_AR_PS_PPROCHIST - "Scheme" '  || 'V00846 Mandatory field'
           END ||
           CASE WHEN LEGACY_CREF_NO IS NULL 
           THEN '^wrk_AR_PS_PPROCHIST - LEGACY_CREF_NO '  || 'V00847 Mandatory field'
           END ||
           CASE WHEN ... etc.
            as error_summary
    from   wrk_ar_ps_pprochist  
  )
  where  error_details is not null
)
on    ( w.id = v.id )
when matched then update
  set w.ErrorMessage = v.error_details,
      w.ErrorMessage_Summary = v.error_summary;
复制

如果您想了解有关如何加快更新的更多详细信息，请在大约45分钟后观看我最近的更新“办公时间”会话：



请注意，在Oracle Database中，空字符串为空，因此不需要所有NVL （...，''）或NVL （...，''） =''复杂化。只需检查表达式是否为NULL即可。

Would putting in a loop once round the table with occasional commits be the best approach?

不可能