Home » RDBMS Server » Performance Tuning » How to delete records without generating redo logs? (Oracle 10g)
How to delete records without generating redo logs? [message #350522] Thu, 25 September 2008 05:11 Go to next message
sudheer0886
Messages: 7
Registered: September 2008
Junior Member


Hi Guru's

I need to delete huge amount of data from a table without actually generating the redo logs.Because in no case I want to rollback again.

So could you provide any solution to do it.

Also please provide me the steps to perform DELETE fastly and efficiently.

Thanks in anticipation
Re: How to delete records without generating redo logs? [message #350528 is a reply to message #350522] Thu, 25 September 2008 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Logging is NOT for rollback, it is agains failure, you can't avoid logging on delete.

Quote:
Also please provide me the steps to perform DELETE fastly and efficiently.

There are many depending on number of rows you have and you want to delete, partitioning or not...

Regards
Michel

Re: How to delete records without generating redo logs? [message #350529 is a reply to message #350522] Thu, 25 September 2008 05:27 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

But Oner alternative here .


:thumpup:
Rajuvan.
Re: How to delete records without generating redo logs? [message #350534 is a reply to message #350522] Thu, 25 September 2008 05:39 Go to previous messageGo to next message
sudheer0886
Messages: 7
Registered: September 2008
Junior Member
Thanks for the information.

But I want to know what is the best way to delete records from table without worrying about the rollback.

Because I want to delete a huge database quickly without carrying about the rollback and redo logs .

Thnaks..
Re: How to delete records without generating redo logs? [message #350536 is a reply to message #350534] Thu, 25 September 2008 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There are many [ways] depending on number of rows you have and you want to delete, partitioning or not...

First detail your case.

Regards
Michel
Re: How to delete records without generating redo logs? [message #350539 is a reply to message #350522] Thu, 25 September 2008 05:57 Go to previous messageGo to next message
sudheer0886
Messages: 7
Registered: September 2008
Junior Member

There are 30 table which are linked through DELETE CASCADE constraint which have 10 million records in them.

So I am trying to delete from the parent table and it records are getting deleted in all the tables.

The problem is it is taking more time.

So i want to know whether there is any option like NOLOGGING on delete so that I delete them quikly
Re: How to delete records without generating redo logs? [message #350541 is a reply to message #350539] Thu, 25 September 2008 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So i want to know whether there is any option like NOLOGGING on delete

NO I already said that.

Regards
Michel

Re: How to delete records without generating redo logs? [message #350562 is a reply to message #350528] Thu, 25 September 2008 07:47 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

You can truncate all the tables. But you have to disable referential integrity constraints and enable after truncating. This is faster, but you cannot undo the action.

Thanks and regards,

MSMallya

[Updated on: Thu, 25 September 2008 07:48]

Report message to a moderator

Re: How to delete records without generating redo logs? [message #350564 is a reply to message #350562] Thu, 25 September 2008 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can truncate the tables.

Not if you want to keep some rows.

Regards
Michel
Re: How to delete records without generating redo logs? [message #350565 is a reply to message #350562] Thu, 25 September 2008 07:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Deleting via CASCADE DELETE constraints is the SLOWEST way to delete data.

TRUNCATE is the best if you want to get rid of every row.

If you want to get rid of >10% of the rows, you will almost certainly find it better to rebuild a copy of the table with CREATE TABLE AS SELECT and omit the unwanted rows.

If you absolutely have to DELETE, use programs to load temporary tables with a list of ROWIDs that you want to remove, and then remove them in a single DELETE statement.

Ross Leishman
Re: How to delete records without generating redo logs? [message #350575 is a reply to message #350522] Thu, 25 September 2008 08:17 Go to previous messageGo to next message
sudheer0886
Messages: 7
Registered: September 2008
Junior Member
If you absolutely have to DELETE, use programs to load temporary tables with a list of ROWIDs that you want to remove, and then remove them in a single DELETE statement.


Ross,

Could you make it clear , how exactly to do that.

Thanks
Re: How to delete records without generating redo logs? [message #350693 is a reply to message #350575] Thu, 25 September 2008 22:55 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
INSERT INTO temp1
SELECT pkcols, rowid
FROM tab1
WHERE whatever;

INSERT INTO temp2
SELECT pkcols, ROWID
FROM tab2
WHERE fkcols in (SELECT pkcols FROM temp1);

INSERT INTO temp3
SELECT pkcols, ROWID
FROM tab3
WHERE fkcols in (SELECT pkcols FROM temp2);

DELETE FROM temp3
WHERE ROWID IN (select rowid FROM temp3);

DELETE FROM temp2
WHERE ROWID IN (select rowid FROM temp2);

DELETE FROM temp1
WHERE ROWID IN (select rowid FROM temp1);


Ross Leishman
Previous Topic: Slow Execution Stored Procedure with cursors
Next Topic: taking to much time to update
Goto Forum:
  


Current Time: Sat Dec 10 07:03:14 CST 2016

Total time taken to generate the page: 0.10373 seconds