RE: Nologging clause

From: Yasin Baskan <>
Date: Wed, 1 Apr 2009 14:18:10 +0300
Message-ID: <FB8D7673B29D504EB6A5988D7F0320CD1E889D8F04_at_PATARA.kfs.local>

Just to add one more thing, nologging operations also depend on the force_logging setting of the database and the tablespaces. If the database is in force logging mode no nologging operations will be possible. They will resort to logging mode. Force logging mode can also be set at the tablespace level, database wide setting overwrites the tablespace level setting.

So, to get nologging operations we also need to check the database and tablespace logging modes.

From: [] On Behalf Of Riyaj Shamsudeen Sent: Wednesday, April 01, 2009 6:24 AM
Cc:; William Wagman; Subject: Re: Nologging clause

There are few more oddities:

  1. If there are any indices on these tables, direct path insert generates redo for those indices. Of course, IOT gets almost no benefit of direct path inserts.
  2. If there are any row level triggers on the table, direct path insert resorts to conventional mode redo generation.
  3. Any foreign key constraint on the table also can resort to conventional mode redo generation
  4. There are some oddities with lob values when stored out-of-line too. With introduction of securefile in 11g, much seems to have changed. There are three sorts of logging now: logging|nologging|filesystem_like_logging.
  5. Merge statement also can do direct path inserts..

 I presented some of it in RMOUG training days presentation (slides 30-36):

  Of course, that is for 9i/10g. I haven't tested it for 11g though..


Riyaj Shamsudeen
Principal DBA,
Ora!nternals - Specialists in Performance, Recovery and EBS11i Blog:

On Tue, Mar 31, 2009 at 7:39 PM, Bobak, Mark <<>> wrote: To add to Greg's answer a bit, there are a few other operations that are "direct path", in addition to INSERT /*+ APPEND */.

Off the top of my head:
index creation/rebuild
create table as select ...

And there may be a few others. Any non-append insert, any delete or update, will *always* log.

Hope that helps,


PS As far as insert /*+ append */ goes, it only applies to a multi-row insert, like insert /*+ append */ into tab select * from tab2; Something like insert /*+ append */ into tab values(...); WILL log.

From:<> [<>] On Behalf Of Greg Rahn [<>] Sent: Tuesday, March 31, 2009 8:26 PM
To: William Wagman
Cc:<> Subject: Re: Nologging clause
This is correct. The [NO]LOGGING only changes direct path operations.  That is, inserts with the /*+ APPEND */ hint.

So if you want NOLOGGING you need either: - NO ARCHIVE LOG mode

On Tue, Mar 31, 2009 at 5:04 PM, William Wagman <<>> wrote:
> It appears that he is saying that if a table is set to nologging and my insert mode is append then even if archive logging is on no redo will be generated. I'm still not sure I am understanding this correctly but I take that to mean
> SQL> insert /*+ APPEND */ into <table>;
> Then no redo will be generated. Is that a correct understanding?

Greg Rahn


Received on Wed Apr 01 2009 - 06:18:10 CDT

Original text of this message