Home » SQL & PL/SQL » SQL & PL/SQL » Tune the plsql (oracle 10 g)
Tune the plsql [message #393374] Sun, 22 March 2009 06:33 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi friends,
I have a plsql procedure like the following


create procedure xyz is

cursor c is
select * from tab_1;

begin

for r_c in c loop
insert into tab_2 values (r_c.empname,r_c.empno);
commit;
end loop;

exception
end;


This procedure is working fine. But its taking a lots of time to complete the excution . Because the table tab_1 contains more than 100000 records.
Should I put the commit in the loop ,after insert syntax or not.
what is the best way to tune it.

Thanks in advance!!
Re: Tune the plsql [message #393375 is a reply to message #393374] Sun, 22 March 2009 06:43 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Im not good in tuning, but try this things if this makes it faster, search for:

INSERT...SELECT statement
BULK COLLECT
FORALL


Re: Tune the plsql [message #393377 is a reply to message #393375] Sun, 22 March 2009 07:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Good advice from @ehegagoka

Read this http://www.orafaq.com/node/1399

Ross Leishman
Re: Tune the plsql [message #393398 is a reply to message #393374] Sun, 22 March 2009 09:42 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Should I put the commit in the loop ,after insert syntax or not.
AFTER.
row by row, is slow by slow
insert into tab_2 select * from tab_1;
COMMIT;
Above is all you need without any explicit cursors.
Previous Topic: Error in code
Next Topic: How to select into a table variable with a table function
Goto Forum:
  


Current Time: Sat Dec 10 20:18:04 CST 2016

Total time taken to generate the page: 0.09924 seconds