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: 25578
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: 25578
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: 3727
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: look for missing record from the same table
Next Topic: Prolem in executing the code.
Goto Forum:
  


Current Time: Wed Aug 23 03:15:41 CDT 2017

Total time taken to generate the page: 0.07168 seconds