Using NOLOGGING for large batch jobs

[mhaller]

We have a large batch job that transfers large amounts of data. One problem we have been having is the amount of redo being generated. I do not want to turn archiving off on our production database and recommend nologging. Our concerns are:

1) Logging will continue to happen for indexes and tables that contain a primary key constraint. To correct this, the primary key constraint would have to be disabled and indexes would also need to have the NOLOGGING option turned on.

2) If the primary key constraint is disabled, this raises many concerns with data integrity and may cause business rules to fail.

3) Also, if the primary key constraint is disabled this also disables the index used for the primary key – hurting performance even more.

How do we address these concerns in order to use nologging?

[mbaliga]

Hi,

Nologging clause is fine for temprary tables where you are not concerned if some data is lost or integrity of data. If its a batch job and transferring large amount of data, I would leave it as it is and addres the space in archive directory by setting up monitoring jobs which will either backup and delete logs or move them to another directory.

Baliga

[mhaller]

Thanks. That's what I was leaning towards, but since I saw several posts of Metalink from other people doing this, I thought I'd found out how they handled it.

[grjohnson]

LOGGING will continue to occur regardless if the tables/tablespaces are in NOLOGGING mode. NOLOGGING doesn't affect normal data inserts, update, deletes etc.

Also, even in NOARCHIVELOG mode, it doesn't stop LOGGING... all changes to the DB are logged, it's just that the LOGS arn't archived.

Good luck!

Cheers,

[Edited by grjohnson on 02-13-2002 at 08:23 PM]