Re: NOLOGGING Option on Create Table

In article <20020406.112841.480298490.1846_at_worldnet.att.net>, "Tom says...
>
>I included the NOLOGGING option on a "create table ..." statement, but it
>is not having the desired affect. Updates to the table are still being
>logged and archived.
>
>The problem is that a vendor product is using an Oracle table as a
>"process queue" which is generating too many redo log entries, and
>subsequently too many archived redo logs. During our tests, we are
>archiving 30MB per minute for a 100 person simulation. There will be over
>500 persons on the system in production. I want to reduce this archiving
>to minimize logger overhead and to conserve disk space.
>
>Should this option have the affect I'm looking for? We have also thought
>of moving the table to its own database with noarchivelog, but that seems
>like overkill. Seems there should be dsome way to do this. The rows in
>this table are transitory and I will never need to recover them.

As Sybrand pointed out -- nologging affects only a few specialized BULK operations. Normal inserts/updates deletes are ALWAYS logged.

I would just like to add my two cents.

I see people say things like "and *I* will never need to recover them". That is wrong. You will need to recover that many times in your lifecycle.

o powerfailure. system goes down. database is aborted. Upon restart we need to do crash recovery. If we did not have redo on this table -- then you would

1. lose some work that needed to be done (rows committed but not yet processed)
2. re-process rows that were already processed (rows deleted but not yet removed from the blocks on disk)

In short -- your entire database would be toast.

o media failure. The disk upon which this queue table resides blows up -- goes away. Once again -- you will be in the position of the above point. This very very important application table would be in some unknown state. Since they appear to be using it to drive the processing for a major portion of the system, your entire database would be logically corrupt. Here if you are not in archivelog mode on this *TABLE* (supposing you could do that), you would have to restore the entire database back to the point in time of the last full cold backup in order to make it logically consistent.

o software failure -- something crashes the instance. See point number 1 above.

People who say "i don't want need to recover this" haven't really thought it all of the way out. You DO in fact need to recover this.

this process queue sounds like a very vital part of their appliation. If it gets out of sync with the rest of the system -- you will MISS doing somethings, and do some things TWICE. Both of which will logically corrupt the rest of the database. Recovery on this object is pretty important.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp