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