Re: Unlogged Deletes in Oracle

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<41ac4dee$0$8929$afc38c87_at_news.optusnet.com.au>...
> 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).

Another suprisingly efficient option is to

CREATE TABLE new_table NOLOGGING AS
SELECT *
FROM old_table
WHERE good_data;

CREATE INDEX as_needed NOLOGGING;

DROP TABLE old_table;

ALTER TABLE new_table RENAME old_table;

I'm sure that I mucked up a bit of the above syntax (rename, perhaps) because I'm typing it in from memory without a manual at hand, but none the less you get the idea. It does entail creating your indices from scratch and, as mentioned above, all of the NOLOGGING of course merits a cold backup.

Cheers,
Scott

>
> > 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 - 10:08:23 CST