OraFAQ Forum: Server Administration » delete data in a table

Home » RDBMS Server » Server Administration » delete data in a table (10.2.0.2)

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

delete data in a table [message #606919]

Thu, 30 January 2014 14:45

varunvir
Messages: 389
Registered: November 2007

Senior Member

Hi Experts,
I have a table with 40 millions records.I want to delete 80% of the data.
What would be the most efficient way for me to delete it.
Thanks,
Varun

Report message to a moderator

Re: delete data in a table [message #606922 is a reply to message #606919]

Thu, 30 January 2014 14:49

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Copy the data you want to keep in another table, truncate the table, copy the data back.

[Updated on: Thu, 30 January 2014 14:49]

Report message to a moderator

Re: delete data in a table [message #607227 is a reply to message #606922]

Tue, 04 February 2014 01:59

mrkamranumer
Messages: 11
Registered: January 2014

Junior Member

Create a Table with

create table t2 as Select * from t1;

truncate 80% from t1 based on where clause and test is this working

Table t2 is a backup table for your original t1 table (you can use it when you need recovery)

Thanks

Report message to a moderator

Re: delete data in a table [message #607230 is a reply to message #607227]

Tue, 04 February 2014 02:10

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

truncate 80% from t1 based on where clause and test is this working

You cannot truncate 80%; you can ONLY truncate 100%.

Quote:

create table t2 as Select * from t1;

Wrong!You copy ONLY what you want to keep.
How what you posted will speed up a single delete of 80% of the original table?
This is the actual question: "What would be the most efficient way for me to delete it."

I did post the correct answer.

Report message to a moderator

Re: delete data in a table [message #607287 is a reply to message #607227]

Tue, 04 February 2014 09:57

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

If you provide a test-case, i.e. DDL to create the existing table and DML to populate it with data, someone here will be able to demonstrate the correct solution that Michel provided.

Report message to a moderator

Re: delete data in a table [message #607672 is a reply to message #607230]

Mon, 10 February 2014 19:19

varunvir
Messages: 389
Registered: November 2007

Senior Member

Thanks Michel!!I got it!!

Report message to a moderator

Previous Topic:	row archival visibility session parameter
Next Topic:	Provide Oracle 8i Software Download link

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 21:54:19 CST 2024