Re: Unlogged Deletes in Oracle

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:23:14 CST