Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Suggestions for improving this update in batches of 100 records

116618Jan 4 2006 — edited Jan 6 2006
I'm currently using the following PL/SQL code to update batches of records in groups of 100 records at a time.

- DB Version : Oracle9i Enterprise Edition Release 9.2.0.7.0
- There is an index on columns : AHS_CONTACT_TYPE, SYNCSTATUS
- I have to commit the records in batches of 100


-- Mark all the Agents "In Process" (regardless of Owner)
-- Update last_modified_by_id to 'SALESFORCE_LOADED' if the
-- last_modified_by_id column is 'SALESFORCE_SYNC' and
-- the ID column is NOT NULL
   ln_count := 0;
   FOR C IN (SELECT tmpsf_CONTACT.ROWID 
               FROM tmpsf_CONTACT 
              WHERE ( AHS_CONTACT_TYPE = c_sfContactType_AGENT ) AND
                     ( SYNCSTATUS <> c_sfsyncstatus_IN_PROCESS )
            )
   LOOP

      UPDATE tmpsf_CONTACT
         SET SYNCSTATUS = c_sfsyncstatus_IN_PROCESS,
             LAST_MODIFIED_BY_ID = decode( LAST_MODIFIED_BY_ID, c_username_SALESFORCE_SYNC,
                                                        decode( ID, NULL, LAST_MODIFIED_BY_ID, 
                                                                    c_username_SALESFORCE_LOADED),
                                                        LAST_MODIFIED_BY_ID)
       WHERE ( tmpsf_CONTACT.ROWID = c.ROWID );
       
      -- Commit every 100 records        
      IF (ln_count >= 100) THEN
         COMMIT;
         ln_count := 1;
      ELSE
         ln_count := ln_count + 1;
      END IF; 
      
   END LOOP;
-- Catch last batch with any records less then 100
COMMIT;
Does anyone have any suggestions about further improving this performance?

Thanks,
Jason
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 3 2006
Added on Jan 4 2006
11 comments
820 views