OraFAQ Forum: Performance Tuning » DELETE hugh data from very big table

Home » RDBMS Server » Performance Tuning » DELETE hugh data from very big table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DELETE hugh data from very big table [message #186644]

Tue, 08 August 2006 17:12

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,

Report message to a moderator

Re: DELETE hugh data from very big table [message #186647 is a reply to message #186644]

Tue, 08 August 2006 17:46

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

Report message to a moderator

Re: DELETE hugh data from very big table [message #186649 is a reply to message #186644]

Tue, 08 August 2006 20:07

Mahesh Rajendran
Messages: 10708
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=

Report message to a moderator

Re: DELETE hugh data from very big table [message #186768 is a reply to message #186647]

Wed, 09 August 2006 07:31

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,

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 05:39:36 CST 2024