Click here to Skip to main content
15,908,674 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am using following procedure to update MASTER table from TEMP table
it takes 5 to 6 hrs to execute.
Can you please help me to optimise time.

TEMP : select in CURSOR (COUNT: 1.5 CR)
MASTER : to update from TEMP table using FOR LOOP (COUNT : 3 CR)
ERROR_LOG : LOG error occured in UPDATE
Field Datatype details :ID1 : Number
ID2 : Varchar2
ID3 : Varchar2
Create or Replace procedure PROC_UPDATION (updbatch number) is --updbatch to record batch size
l_rows_affacted NUMBER := 0;
l_Total_rows_affacted NUMBER := 0;
l_Totrec NUMBER:=0;
str varchar2(255);
CURSOR temp_cur IS
SELECT ID1,ID2,ID3 from TEMP; --TABLE 1 has 1 cr rows.
FOR temp_rec in temp_cur LOOP
DECLARE  --internal block start to continue Loop even if Exception occures.
updexception Exception;
if temp_rec.ID1 is NULL then 
    raise  updexception;
   end if;
   str:='update master set ID1 ='|| temp_rec.ID1 ||' where ID2 ='''|| ID2 ||''' and ID3='''|| ID3 ||'''';
   execute immediate str;
  --l_rows_affacted returns 1 id 
   --update successful and 0 if record not updated
  l_rows_affacted := SQL%ROWCOUNT;   EXCEPTION
   WHEN updexception then
   l_Total_rows_affacted:=l_Total_rows_affacted -1;
   insert into error_log values(sysdate,'UPDATE FAILED',LOG_SEQ.NEXTVAL);
END LOOP; --Loop End
 when others then
 insert into error_log values(sysdate,str,LOG_SEQ.NEXTVAL);?commit;

[edit]SHOUTING removed, Code block added - OriginalGriff[/edit]
Updated 21-Nov-12 1:46am
OriginalGriff 21-Nov-12 2:56am    
DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalisation if you want to be taken seriously.
ixashish 23-Nov-12 5:59am    
Hello this is not intentional i oplogize if anybody hurt..
ixashish 23-Nov-12 6:01am    
do you have any solution for problem?
Banerjee Tanmoy 28-Dec-12 6:27am    
Instead of doing update better approach is delete and insert.(Much faster)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900