Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Unlogged Deletes in Oracle

Re: Unlogged Deletes in Oracle

From: <>
Date: 2 Dec 2004 00:22:32 -0800
Message-ID: <>

Hi Howard,

"But think about it logically. If you could ever (ordinarily) suppress redo generation for deletes, and there was some sort of failure, how would Oracle ever know which records in a table should have been deleted
and which not? We'll have to wait for dbms_mindreading before that can be resolved!"

I will need to check, because it has been so long, but I am pretty sure I was doing exactly this with DB2/MVS in the early 90s.

Thinking about it logically. Say I am a DBA (I'm not any more). I need to update 60M rows in a partition of 500M rows. I know, by the nature of the data, for sure and for certain, that every row coming to me is already in the partition. I can take a 'low priority' backup in the background during the day with minimal impact on the users. Then, when it comes time to apply the 60M updates what can I do? If the database supports unlogged updates (which only Sybase IQ does as far as I am aware) then I can just apply the 60M updates no problems. Then I back up the partition again...if the update falls over, no problem, I have a backup and I restore the whole partition, then I start the update again. It will fail rarely enough not to be a problem one would hope!!!!

If the database does not support unlogged updates I cannot just apply 60M updates. The logging will be too much. I need to find a way to apply the updates so that no logging takes place. One way to do this is to perform unlogged deletes and then a load of the full updated record. Like I said, I seem to remember doing this 10 years ago on MVS, but I could be wrong....

Certainly, having been a vendor, I take your point that we should not use 'undocumented features' that may well break such a large partition. Which was why I was looking for a 'supported' solution in Oracle 9.2... :-) Received on Thu Dec 02 2004 - 02:22:32 CST

Original text of this message