Avoid Cursor Loops

Fetching the data set using a CURSOR initially and performing updates one at a time subsequently, is the best practice when it comes to PL/SQL business logic code. However, it is not the same when it comes to upgrades! Executing multiples of data records sequentially will consume substantial amount database resources and overall upgrade performance will be affected.

Technique Inherited from Business Logic

DECLARE
   CURSOR dm_record IS
         SELECT *
         FROM   xlr_dimension_source_tab t
         WHERE  dimension_id = 'DIM_WORK_ORDER'
         AND    source_type = 'DataMart'
         AND NOT EXISTS(SELECT 1 
                        FROM   XLR_DIMENSION_SOURCE_TAB t
                        WHERE  dimension_id = 'DIM_WORK_ORDER'
                        AND    source_type  = 'OnLine');
BEGIN
   FOR rec_ IN dm_record LOOP
       INSERT INTO xlr_dimension_source_tab 
              (dimension_id, 
               source_type, 
               default_source_type, 
               view_name, 
               rowversion)
       VALUES (rec_.dimension_id,
               'OnLine',
               0,
               rec_.view_name,
               1);
   END LOOP;
   COMMIT;
END;
/

In this case multiple of single record INSERTs are submitted to the database leading to large number of context switches and consequently, an additional overhead to it.

Direct SQL Statement

Instead of having a PL/SQL block as above, the best practice would be to issue a single insert statement as follows:

INSERT INTO xlr_dimension_source_tab(dimension_id, source_type, default_source_type, 
                                     view_name, rowversion)
                              SELECT dimension_id, 'OnLine',    0,                   
                                     view_name, 1
                              FROM   xlr_dimension_source_tab t
                              WHERE  dimension_id = 'DIM_WORK_ORDER'
                              AND    source_type = 'DataMart'
                              AND NOT EXISTS(SELECT 1 
                                             FROM   XLR_DIMENSION_SOURCE_TAB t
                                             WHERE  dimension_id = 'DIM_WORK_ORDER'
                                             AND    source_type  = 'OnLine');

COMMIT;

As this statement will be submitted to the database as a single action, it will execute more efficiently.

UPDATE Statements

UPDATE statements nested inside loops can also be reduced into a single SQL statement with multiple record modifications by following a similar procedure.

Further Information

Please refer to DML on Multiple Records for more information.