Re: Unlogged Deletes in Oracle

Peter Nolan wrote:
> Hi All,
> I am coming to grips with what 'nologging' means in Oracle.

Practically nothing as far as regular DML on regular tables is concerned. It applies to certain special data load operations (an insert fone with an APPEND hint, for example; or a direct path SQL Loader load). It applies to some sorts of partition DDL. And it applies to index creation operations. But regular DML on regular tables *always* generates redo, whatever NOLOGGING happens to be set to.

> I have a table with only 25M rows but lots of columns and lots and
> lots of indexes on it. I'm trying to get rid of about 8M rows but the
> delete is writing about 3GB per 500K rows deleted to the undo
> tablespace. We have set the undo retention parameter to 60 seconds and
> bounced the database but the undots stays populated with undo data
> unless we wait for what seems like overnight. (It's empty in the
> morning when we come in...)
>
> I don't want to recreate the table as we have people testing and it
> would take 24 hours to rebuild...
>
> Q1. Can anyone tell me the best way to perform minimal logging on
> deletes? (I have another client where we know we will want to do a lot
> of updates and we may want to do these as unlogged deletes and a
> load.)

If you want to invalidate your support contract, and are prepared to lose your entire database, there are ways not to generate redo, but I presume those are not really options. Therefore, redo generation by these deletes is something you will just have to provision for. Because deletes, as a regular piece of DML, always generates redo.

That said, you could, of course, drop all those unnecessary indexes first. A drop segment command generates very little redo. And if the indexes aren't there at the time you start deleting from the table, then they can't generate redo as a result of the delete. Of course, once the table DML is finished, you will have to re-create all the indexes afterwards (here's a tip: find out which indexes you can actually live without, and don't rebuild those ones!)... though as I mentioned, for index creation, a nologging attribute on the index itself *would* be respected (create index blah nologging on X(col)).

Don't forget that best practice after any nologging operation that actually does indeed generate no redo is to perform a new backup. (IE, you never actually win with Oracle: the time and effort you save on the DML side of things is usually expended on the index creation/extra backup side of things).

> Q2. And how can we clear out the undots after each set of deletes?

Why would you want to? Undo recycles itself, provided that the transaction that generated it no longer needs it. If you keep committing, then you mark the undo generated by the deletes up to that point as over-writable (after undo_retention seconds, true enough). If you are currently trying to delete all 25M rows in one transaction, then you might want to re-think things somewhat.

What is the actual problem you are having with undo generation?

Regards
HJR
> We are on AIX 5.1 and Oracle 9.2 64 bit version.
>
> Thanks
>
> Peter Nolan
Received on Tue Nov 30 2004 - 04:39:41 CST