Home » SQL & PL/SQL » SQL & PL/SQL » large data deletion (oracle,9.2.0.8,solaris 9)
large data deletion [message #291587] Sat, 05 January 2008 03:45 Go to next message
vishal_srivastava
Messages: 21
Registered: January 2008
Junior Member
i have to delete data from a table which contains 36000 rows.
suggest me the fastest method.
should i use any procedure?
Re: large data deletion [message #291588 is a reply to message #291587] Sat, 05 January 2008 03:51 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

just a simple delete statement.what makes you think that 36k rows are too large?


regards,
Re: large data deletion [message #291590 is a reply to message #291588] Sat, 05 January 2008 03:53 Go to previous messageGo to next message
vishal_srivastava
Messages: 21
Registered: January 2008
Junior Member
Ok fine 36k rows are not large.
if i have much more than that then?
Re: large data deletion [message #291591 is a reply to message #291590] Sat, 05 January 2008 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It depends on how many, on server power, on percentage you want to delete, on indexes you have...

Regards
Michel
Re: large data deletion [message #291754 is a reply to message #291587] Sun, 06 January 2008 11:05 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
there is way more to this questions that meets they eye. Riddle me this:

Is 36000 rows, all the rows in the table?
What are you going to do after you delete the rows, put more back?
Is the delete part of a bigger transaction? if so, what should happen to the deleted rows if the transaction fails? (rollback I assume).

There are essentially four basic ways to get rid of rows from a table;

1) drop table x;
2) truncate table x;
3) delete from table x where (rows to delete);
4) create table x2 as select * from x where NOT (rows to delete);


CONTEMPLATE THE DIFFERENCES OF EACH APPROACH.

Only #3 runs inside the context of a transaction. All other options though potentially way faster in the right circumstances, are their own little transaction. You must account for this somehow if you try to use them.

So, vishal_srivastava, what can you tell us about the transaction this delete will live in?

Good luck, Kevin
Previous Topic: Why, dml_locks disabled?
Next Topic: Excluding records in result set
Goto Forum:
  


Current Time: Sat Nov 09 14:07:00 CST 2024