Re: Nologging clause
Date: Tue, 31 Mar 2009 22:24:19 -0500
There are few more oddities:
- 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.
- If there are any row level triggers on the table, direct path insert resorts to conventional mode redo generation.
- Any foreign key constraint on the table also can resort to conventional mode redo generation
- 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.
- Merge statement also can do direct path inserts..
I presented some of it in RMOUG training days presentation (slides 30-36): http://orainternals.files.wordpress.com/2008/04/riyaj_debunking_myths_about_redo_ppt.pdf
Of course, that is for 9i/10g. I haven't tested it for 11g though..
On Tue, Mar 31, 2009 at 7:39 PM, Bobak, Mark <Mark.Bobak_at_proquest.com>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: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On
> Behalf Of Greg Rahn [greg_at_structureddata.org]
> Sent: Tuesday, March 31, 2009 8:26 PM
> To: William Wagman
> Cc: oracle-l_at_freelists.org
> 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
> - ARCHIVE LOG mode, NOLOGGING, and APPEND hint
> On Tue, Mar 31, 2009 at 5:04 PM, William Wagman <wjwagman_at_ucdavis.edu>
> > 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