Home » SQL & PL/SQL » SQL & PL/SQL » deleting 10 million records form a table (oracle 10.2.0.2 solaris 10 64 bit)
deleting 10 million records form a table [message #317373] Wed, 30 April 2008 20:05 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Hai,

i want to delete 10 million records form one table. what is the best way to do it. i am deleting records based on one condition.

what are all the best possible way to delete. can any one help me to do it effictive way.

Thanks ,
kumar
Re: deleting 10 million records form a table [message #317374 is a reply to message #317373] Wed, 30 April 2008 20:11 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
DELETE FROM MY_TABLE WHERE DELETED = 1;
COMMIT;
Re: deleting 10 million records form a table [message #317379 is a reply to message #317373] Wed, 30 April 2008 20:36 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Of course, if you are going to be deleting most of the rows, then it might be quicker to do a three-step operation: (1) select the rows you want to keep into a scratch table, (2) truncate the original table, and then (3) put the good rows back.

You also might investigate dropping and recreating the indexes on the table.
Re: deleting 10 million records form a table [message #317381 is a reply to message #317373] Wed, 30 April 2008 20:42 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>then it might be quicker to do a three-step operation:
Yes, it might be quicker, but it would break the application most likely while this process completed.

>You also might investigate dropping and recreating the indexes on the table.
What is the metric & threshold value that changes the action from not dropping and recreating the indexes to actually dropping and recreating the indexes?
Re: deleting 10 million records form a table [message #317383 is a reply to message #317381] Wed, 30 April 2008 22:42 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
anacedent wrote on Thu, 01 May 2008 11:42

What is the metric & threshold value that changes the action from not dropping and recreating the indexes to actually dropping and recreating the indexes?

It varies depending on the number and size of indexes, the physical distribution of the deleted rows, and your hardware and DB setup. Probably some other stuff as well.

Even given all of this, I have not encountered a case where deletion of more than 10% of a table larger than 10M rows can be done more efficiently (given an application outage) than a rebuild.

Ross Leishman
Previous Topic: Oracle timestamp
Next Topic: Exiting an SQL Application
Goto Forum:
  


Current Time: Tue Dec 06 12:20:32 CST 2016

Total time taken to generate the page: 0.09607 seconds