Re: Unlogged Deletes in Oracle

Hi Howard,

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

> And it applies to
> index creation operations. But regular DML on regular tables *always*
> generates redo, whatever NOLOGGING happens to be set to.

>>> Yes, I found out a while ago that 'Nologging' seems to mean 'not
as much logging as it usually does...'

>

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

>>> While we are testing, not a problem... ;-) Hopefully in production
we won't be deleting anything....I'm just very surprised at the volume of undo data and the very slow speed of these deletes. We are on a 5 processor AIX box and it can take 30 minutes or more to delete 1M rows..... (Reminds me of the SQL Server 7 logging problems....)

>
> 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)).
>
>>> Well, I did drop all the indexes on the weekend, but had not
decided to delete these rows at the time...The index rebuild was amazingly slow at 6 hours to rebuild the 30+ indexes. All indexes were rebuilt with NOLOGGING. We are going through the process of partitioning this specific table but I would have thought at only 25M rows the extra work of partitioning would not be needed....it's a small table. We are still to go through our 'rationalise the indexes' task....we have just been creating indexes as reports need them and not paying too much attention to 'similar' indexes. In the end we will have this huge slab of SQL in reports and we can run performance testing across them.

>
> > 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.
>
>>>Nope, we are only trying to take 8M rows and put them into another
table...we did the select insert to copy the rows to the other table, and we are deleting the 8M because we don't need them in the old table any more. We have built this data mart so that any 'segment' of this larger table can be relocated to a single table with no changes to any ETL/reports......and it all works fine, except when we tried to delete the old data....

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

>> Just the massive amount of processor and disk used to delete rows.
When doing DWing, in many cases you know that you can delete a bunch of rows because you are going to re-load them. I seem to recall Oracle have 'touted' that this problem was solved in 9i with 'nologging', which I thought meant 'no logging' as an option you can turn on (silly me ;-)). But this does not seem to be the case. It still seems like when we need to replace a large number of rows, it will be faster to truncate the table and reload the partition than to delete the rows to be updated with no-logging and reload just those rows....Seems kind of strange that all deletes should be logged like this and no option being available to turn it off...
>
>
> Regards
> HJR
>
> > We are on AIX 5.1 and Oracle 9.2 64 bit version.
> >
> > Thanks
> >
> > Peter Nolan
Received on Tue Nov 30 2004 - 11:29:21 CST