Re: Unlogged Deletes in Oracle

peter_at_peternolan.com wrote:
> 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!!!!

What you are actually describing is an update process that says 'to hell with recoverability! If it goes wrong, we just start over (ie, restore the backup).' So you've answered my question quite nicely with the right answer: "It can't (know which records should have been [updated] and which not...". And we resolve the ensuing dilemma by brute force.

Well, that is the option I was describing that is still available in Oracle, and which I've actually helped implement in a production setting, just once. Backup before a bulk operation, switch off all redo, perform bulk operation, be prepared to restore the entire backup and start from scratch if anything goes wrong. And remember to switch redo generation back on afterwards! (Oh, and don't forget the fresh backup afterwards, too).

It's entirely logical, but unfortunately, as I said, not supported.

Oh, and there's a slight catch: because you are about to switch off redo generation, it isn't good enough to do a 'low impact' prior backup (ie, presumably, a hot backup). It's a 'shutdown and do a cold, complete backup' operation. Otherwise there's a risk that the hot backup wouldn't be recoverable (i.e., usable).

> 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....

As I say, you can do it today in Oracle.

Just don't tell anyone you're doing it, or don't expect any help from official sources, if you do.

Regards
HJR
> 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 - 04:12:52 CST