Home » SQL & PL/SQL » SQL & PL/SQL » how to reduce the time for Package to execute (oracle 10g ,Xp)
how to reduce the time for Package to execute [message #307798] Thu, 20 March 2008 00:28 Go to next message
tannad
Messages: 43
Registered: January 2008
Location: mumbai
Member
Hi all..

I have one package ,
In this package there are 10 procedure called.
when I call the package it will take so much time
I will give u the procedure in the package
when to delete, millions of rows are deleted same way for update
how I reduce the time of executing the package ..It will take so much time


SELECT a.ROWID BULK COLLECT INTO t_rowids FROM a_acct a,
a_location_gl_definition b
WHERE a.location_id = b.location_id
and a.location_id = p_location_id
FOR UPDATE NOWAIT;
--- delete from a_acct table
IF t_rowids.COUNT > 0 THEN
FORALL i IN t_rowids.FIRST..t_rowids.LAST
DELETE FROM a_acct
WHERE ROWID = t_rowids(i);
COMMIT;
DBMS_OUTPUT.PUT_LINE(t_rowids.count||'Records Are Deleted From The a_acct table..');
END IF;


in this way 4 stmt in each proc
Thanks in advance...

Regards,
Tanad



Re: how to reduce the time for Package to execute [message #307812 is a reply to message #307798] Thu, 20 March 2008 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is newbie question: why not just ONE delete?

Regards
Michel

[Updated on: Thu, 20 March 2008 01:15]

Report message to a moderator

Re: how to reduce the time for Package to execute [message #307938 is a reply to message #307812] Thu, 20 March 2008 07:35 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look here

Ross Leishman
Previous Topic: Unions + Grouping
Next Topic: Insert CR into line
Goto Forum:
  


Current Time: Mon Nov 04 04:23:32 CST 2024