Re: Nologging clause

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

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com

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,
>
> -Mark
>
> 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>
> 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?
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l