Home » RDBMS Server » Performance Tuning » DELETE hugh data from very big table
DELETE hugh data from very big table [message #186644] Tue, 08 August 2006 17:12 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I am using oracle 9i and I have a very big table and needs to be delete almost 24M data. What's the best way to delete the data? I have index on my load_date which i am using in my where criteria.

Thanks,
Re: DELETE hugh data from very big table [message #186647 is a reply to message #186644] Tue, 08 August 2006 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>What's the best way to delete the data?
"BEST" based upon what metric?
DELETEs are very expensive WRT consuming ROLLBACK space; because row level INSERTs go into ROLLBACK.
On lower cost method is by partition pruning, but the application must have been designed & implemented with this requirement in mind
Re: DELETE hugh data from very big table [message #186649 is a reply to message #186644] Tue, 08 August 2006 20:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And, search before posting
http://www.orafaq.com/forum/?SQ=beeab575b52afa59b5eb4e96bb8a4d38&t=search&srch=huge+delete&btn_submit=Search&field=al l&forum_limiter=6&search_logic=AND&sort_order=DESC&author=
Re: DELETE hugh data from very big table [message #186768 is a reply to message #186647] Wed, 09 August 2006 07:31 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks. You are right that partition is the best method but this is first time we are going to delete the data and re-writing method to eliminate more reocrds after deleting.
I know there is CTAS method cam alos help but i was wondering if I run straight DELETE or including into pl/sql will be better any another way as i tried to run straight DELETE and after two hrs I have to stop it.

Thanks,
Previous Topic: Replication Issues with external tables
Next Topic: Releasing SYSTEM TABLE SPACE to USERDATA,INDEX
Goto Forum:
  


Current Time: Thu Mar 28 11:06:02 CDT 2024