In the following procedure, the update is taking 90 minutes.
The client table has 22 million records, the CPDS table has 40 records.
It has been written by someone else, I don't have access to Oracle so can't check my thinking, but I believe, if we select the data to be updated into pl/sql tables, and then using rowid just update the rows with the data stored in pl/sql tables (both the select and update using bulk binds), then the query will be faster.
PROCEDURE POPULATE_CLIENT_CPDS_DATA( P_START_CLNTNUM IN NUMBER, P_END_CLNTNUM IN NUMBER, P_UPDATED OUT NUMBER) IS L_STRSQL VARCHAR2(2000) ; BEGIN L_STRSQL := 'UPDATE CLIENT C SET ( O2BFID, MSISDNVALIDATIONDATE, SOURCE ) = ( SELECT O2BFID, MSISDNVALIDATIONDATE, SOURCE FROM C USTOMER_CPDS_DATA WHERE ID = C.ID ) WHERE CLNTNUM BETWEEN ' ;
L_STRSQL := L_STRSQL || P_START_CLNTNUM || ' AND ' || P_END_CLNTNUM || ' AND C.DOMAIN = ''' || 'IFL.co.uk' || ''' AND EXISTS ( SELEC T 1 FROM CLIENT_CPDS_DATA WHERE ID = C.ID)';
EXECUTE IMMEDIATE L_STRSQL ; P_UPDATED := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN P_UPDATED := -1; END POPULATE_CLIENT_CPDS_DATA;
February 1, 2011 4:19 PM
February 1, 2011 5:30 PM