You Asked

I have recently inherited a migration suite that takes a long time to run, so I am looking at the various long running SQL commands to try and improve it. One areas of issue is some data validation that also does updates. Each validation is scanning a table of 6 million items and updating the error message column when it finds an issue. The trouble is there are 15 validations, so 15 scans of the table. I'm struggling to think of a way to combine these into a single database scan, are you able to help please?

I think the code has been written by somebody familiar with SQL but not PL/SQL. Would putting in a loop once round the table with occasional commits be the best approach? rather then trying to cram everything into a single SQL statement.

Here is the code

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)      ;

This table starts empty and is populated, once populated stats are run to help the indexes

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

There are currently 4 indexes
row_uid
Legacy_value_06
Legacy_basic_no
Legacy_basic_no, legacy_value_07

and Chris said...

Instead of writing separate UPDATEs, you can chain together all the changes in one.

Do this by concatenating the CASE expressions in the SET clause, e.g.:

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.;

I'm not sure what the logic would be for setting the Valid column.

If you expect relatively few rows to fail validation (i.e. the update changes few rows), it may be faster to filter down to just the erroring rows, so you only change these. You can do this with correlated UPDATE or MERGE, e.g.:

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;

If you want more details on how to make the update faster, watch my recent Office Hours session on UPDATE from around 45 minute mark:

Note that in Oracle Database the empty string IS NULL, so there's no need for all the NVL ( ..., '' ) OR NVL ( ..., '' ) = '' complication. Just check if the expression IS NULL.

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

Unlikely.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

More to Explore

SQL

Replacing Multiple scans of a table for a series of updates.

Breadcrumb

Question and Answer

You Asked

and Chris said...

More to Explore

SQL