Home » SQL & PL/SQL » SQL & PL/SQL » Purging old records (11gR2 - Linux)
Purging old records [message #657064] Thu, 27 October 2016 05:57 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7261
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 #657070 is a reply to message #657064] Thu, 27 October 2016 06:31 Go to previous messageGo to next message
EdStevens
Messages: 902
Registered: September 2013
Senior Member
I agree with John. 3 hours to delete 2 million is astonishing. Jaw dropping.

Even before a statspack or AWR report (AWR requires Enterprise edition plus additional licensing) I'd want to see the procedure you are using to delete the rows. Is it some kind of procedureal, row-by-row (slow-by-slow) or a single, qualified DELETE statement. Show us some code.
Re: Purging old records [message #657072 is a reply to message #657070] Thu, 27 October 2016 07:08 Go to previous messageGo to next message
Roachcoach
Messages: 1513
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

Re: Purging old records [message #657075 is a reply to message #657064] Thu, 27 October 2016 08:05 Go to previous messageGo to next message
BlackSwan
Messages: 25854
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) ddl for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Purging old records [message #657080 is a reply to message #657075] Thu, 27 October 2016 08:31 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
You didn't fancy editing that to account for the fact that we haven't seen the SQL?
Re: Purging old records [message #657086 is a reply to message #657080] Thu, 27 October 2016 11:51 Go to previous message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Who say SQL? Smile maybe it is a PL/SQL loop (committing each row, of course).

Previous Topic: Query : Need help on populating prev day data in current day if missing
Next Topic: Split Blog column to multiple columns within a row
Goto Forum:
  


Current Time: Tue Feb 20 11:44:32 CST 2018

Total time taken to generate the page: 0.01598 seconds