Home » RDBMS Server » Server Administration » Performance degrade while deleting 1 million row (Oracle 9.2.0.8)
Performance degrade while deleting 1 million row [message #439026] Wed, 13 January 2010 12:08 Go to next message
manish_edu
Messages: 2
Registered: January 2010
Junior Member
Hi ,
I have to delete 1 million row from my table as the data was incorrectly inserted..

It is production system and deleting 1 million row will make the database die and user will have to stuck their operation for long..

Please guide me the best suitable way to delete these rows..
Re: Performance degrade while deleting 1 million row [message #439028 is a reply to message #439026] Wed, 13 January 2010 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
>I have to delete 1 million row from my table as the data was incorrectly inserted..

about how many rows exist in table now, before delete?

>It is production system and deleting 1 million row will make the database die and user will have to stuck their operation for long..

Not necessarily

What tables/operations are dependent upon this table.

do DELETE at 2AM
Re: Performance degrade while deleting 1 million row [message #439040 is a reply to message #439028] Wed, 13 January 2010 12:48 Go to previous messageGo to next message
manish_edu
Messages: 2
Registered: January 2010
Junior Member
It was approx 10 million rows.. we used to update the table with billing information of the user at night .. In the day time users are connected with this table to generate report..
Re: Performance degrade while deleting 1 million row [message #439041 is a reply to message #439040] Wed, 13 January 2010 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
With Oracle WRITERS do not block READERS & READERS do not block WRITERS.

Without knowing more details, a single DELETE statement should have little to no impact on generating reports.
Re: Performance degrade while deleting 1 million row [message #439048 is a reply to message #439041] Wed, 13 January 2010 13:44 Go to previous messageGo to next message
palazzi
Messages: 11
Registered: June 2009
Location: Toluca
Junior Member
Hi

Probably your database "died" because of an undo problem.

Check if you have enough undo space or autoextend activated.

Saludos.
Re: Performance degrade while deleting 1 million row [message #439049 is a reply to message #439048] Wed, 13 January 2010 13:47 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
Yes, UNDO of DELETE are the largest, because the UNDO is a complete row INSERT.

When compared to UNDO of INSERT is a small DELETE ROWID
Re: Performance degrade while deleting 1 million row [message #439064 is a reply to message #439026] Wed, 13 January 2010 22:48 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
you are SURE you want to DELETE

then

Step 1 :- alter table <Table Name> nologging;
Step 2 :- Set AutoCommit <Say 5000>
Step 23:- delete from <Table name>


Step 1 :- No-logging can be used to minimize the amount of redo generated.

Step 2 :- Keep on Commiting data so Roll back segment is not filled and possibly you avoid 1555 error. so you are not back to square 1 Smile

Hope it helps.





Re: Performance degrade while deleting 1 million row [message #439075 is a reply to message #439064] Thu, 14 January 2010 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ NOLOGGING works only on direct path operation which is not the case of a DELETE. (In addition, it implies a backup after the operation.)

2/ Autocommit SQL*Plus feature auto commits after x DML statements not after x modified/deleted rows, so it does nothing in a single DELETE

3/ Committing inside a loop does not avoid ORA-1555 it INCREASES the likelyhood to get it.

Time to review your basics. Smile

Regards
Michel

[Updated on: Thu, 14 January 2010 01:39]

Report message to a moderator

Re: Performance degrade while deleting 1 million row [message #439083 is a reply to message #439028] Thu, 14 January 2010 02:29 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

BlackSwan wrote on Wed, 13 January 2010 19:12
>
about how many rows exist in table now, before delete?

And the answer is ?

How many indexes does the table have ?
Re: Performance degrade while deleting 1 million row [message #439089 is a reply to message #439026] Thu, 14 January 2010 03:22 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Quote:
Time to review your basics.
Cool Sure but some other time Michel.


manish_edu

Check following Link.

Performing Massive DELETEs as Bulk INSERT
Re: Performance degrade while deleting 1 million row [message #439092 is a reply to message #439089] Thu, 14 January 2010 03:49 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To know if alternative way might be the more efficient way, we will have to wait for OP to answer BlakcSwan's question. And the one you pointed to might not be the most efficient one.

In addition, you can't do it in while others are working, and for the moment OP wants to do it without stopping the application (as his sentence "user will have to stuck their operation for long" seems to imply).

Regards
Michel
Previous Topic: About REDO LOG FILE
Next Topic: Oracle 11g Installation
Goto Forum:
  


Current Time: Sun Sep 25 07:51:06 CDT 2016

Total time taken to generate the page: 0.07539 seconds