| Purging old records [message #657064] |
Thu, 27 October 2016 05:57  |
 |
grvenkatesh1986@gmail.com
Messages: 1
Registered: October 2016
|
Junior Member |
|
|
Hi All,
We want to purge the records beyond 6 years record from multiple table and it contains around 1.3+ billion records.
Totally 7 tables records need to purge.
Total no of Records : 1332600278
Total no of days : 2504 [ From 18th Dec 2003 to 25th Oct 2010]
Example : For deleting 2 million records it took 3+ hours time from single table
Could you please help us on the best way to purge records and we need minimize downtime.
Regards,
Venkatesh G R
9952923191
|
|
|
|
| Re: Purging old records [message #657066 is a reply to message #657064] |
Thu, 27 October 2016 06:10  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Three hours to delete two millions rows (please do not say "record" when you mean "row") is astonishingly slow, and you need to investigate why. A Statspack or AWR report covering the three hour period would be a good start.
|
|
|
|
|
|
| Re: Purging old records [message #657072 is a reply to message #657070] |
Thu, 27 October 2016 07:08 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Add enough indexes to a table, potential referential integrity, a small buffer cache and potentially a trigger and I'm not even slightly surprised to be honest. It's well withing the realms of possibility. That's before we get into super wide tables or worse, the hell quaintly noted as LOBs.
Heck I have one in front of me right now with 88 columns and 19(!) indexes, most of which have a blevel of 3, on it mired with RI to boot.
It may also be a logically driven delete whereby the most of the time is going on a select statement. Or a combination of the above.
[Updated on: Thu, 27 October 2016 07:16] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
maybe it is a PL/SQL loop (committing each row, of course).