Home » SQL & PL/SQL » SQL & PL/SQL » best way to optimise Delete syntax (oracle10g)
best way to optimise Delete syntax [message #434300] Tue, 08 December 2009 08:12 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi friends,
Need your help.
delte from table B;

The table B contains billons of records and doesnont have any index.
So the delete syntax taking long hours to execute.

So what is the best way to optimise this. please suggest.
Re: best way to optimise Delete syntax [message #434301 is a reply to message #434300] Tue, 08 December 2009 08:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Add an index.
Re: best way to optimise Delete syntax [message #434302 is a reply to message #434300] Tue, 08 December 2009 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Assuming you want to get rid of all rows:
truncate table B;


You'll have to disable any foreign keys pointing to that table before you do it.
Re: best way to optimise Delete syntax [message #434304 is a reply to message #434300] Tue, 08 December 2009 08:22 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
So the delete syntax taking long hours to execute.
Hmmm, I always thought that an hour was constant Wink
Re: best way to optimise Delete syntax [message #434305 is a reply to message #434300] Tue, 08 December 2009 08:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the table doesn't have any foreign key constraints pointing to it, it might well be quicker to create another table containing only the rows you want to keep, drop the original table, and then rename the new table to the old one.
Re: best way to optimise Delete syntax [message #434307 is a reply to message #434305] Tue, 08 December 2009 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...and recreate the triggers, the indexes, the mviews, regrant the privileges, recompile the packages, views, current cursors...

Regards
Michel
Re: best way to optimise Delete syntax [message #434308 is a reply to message #434307] Tue, 08 December 2009 08:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The cursors are gone for ever, the views an packages will take care of them selves, and the rest of it is just a ton of spadework.

Admittedly, it's spadework that you need to think about before, rather than after, but that's just an implementation detail.
Re: best way to optimise Delete syntax [message #434309 is a reply to message #434307] Tue, 08 December 2009 08:45 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
If I remember correctly in the dim and distant past, (well, maybe a couple of years ago) I had some success with Loading a temp table with the required data, truncating the 'main' table then loading that table up with the data in the temp table. No need for recreating / recompiling dependent objects.
Re: best way to optimise Delete syntax [message #434310 is a reply to message #434308] Tue, 08 December 2009 08:57 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was just a warning on the amount of work the DBA or Oracle will have to do to get back the current situtation.

Regards
Michel
Previous Topic: Query on PL/SQL logic
Next Topic: How to filter multiple records
Goto Forum:
  


Current Time: Sun Sep 25 07:49:14 CDT 2016

Total time taken to generate the page: 0.13710 seconds