Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hello
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.
 
TABLES USED :
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.
BEGIN 
FOR temp_rec in temp_cur LOOP
DECLARE  --internal block start to continue Loop even if Exception occures.
updexception Exception;
BEGIN  
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);
   commit;
  END;  
END LOOP; --Loop End
EXCEPTION
 when others then
 insert into error_log values(sysdate,str,LOG_SEQ.NEXTVAL);?commit;
END;
/
 

[edit]SHOUTING removed, Code block added - OriginalGriff[/edit]
Posted 20-Nov-12 19:20pm
Edited 21-Nov-12 1:46am
86.1K
v3
Comments
OriginalGriff at 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 at 23-Nov-12 5:59am
   
Hello this is not intentional i oplogize if anybody hurt..
ixashish at 23-Nov-12 6:01am
   
do you have any solution for problem?
Tanmoy1 at 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)

  Print Answers RSS
0 Maciej Los 450
1 OriginalGriff 403
2 Sergey Alexandrovich Kryukov 189
3 CPallini 189
4 CHill60 180
0 OriginalGriff 6,092
1 Sergey Alexandrovich Kryukov 4,972
2 Maciej Los 3,269
3 Peter Leow 3,129
4 DamithSL 2,490


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 21 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100