Home » RDBMS Server » Server Administration » delete data in a table (10.2.0.2)
delete data in a table [message #606919] Thu, 30 January 2014 14:45 Go to next message
varunvir
Messages: 323
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
Re: delete data in a table [message #606922 is a reply to message #606919] Thu, 30 January 2014 14:49 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: delete data in a table [message #607230 is a reply to message #607227] Tue, 04 February 2014 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: delete data in a table [message #607287 is a reply to message #607227] Tue, 04 February 2014 09:57 Go to previous messageGo to next message
gazzag
Messages: 267
Registered: November 2010
Location: Bristol, 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.
Re: delete data in a table [message #607672 is a reply to message #607230] Mon, 10 February 2014 19:19 Go to previous message
varunvir
Messages: 323
Registered: November 2007
Senior Member
Thanks Michel!!I got it!!
Previous Topic: row archival visibility session parameter
Next Topic: Provide Oracle 8i Software Download link
Goto Forum:
  


Current Time: Fri Apr 18 02:29:24 CDT 2014

Total time taken to generate the page: 0.08409 seconds