| deleting 10 million records form a table [message #317373] |
Wed, 30 April 2008 20:05  |
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 #317379 is a reply to message #317373] |
Wed, 30 April 2008 20:36  |
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 #317383 is a reply to message #317381] |
Wed, 30 April 2008 22:42 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
