RE: Nologging clause
- From: Yasin Baskan <yasin.baskan@xxxxxxxxxxxxxxxx>
- To: "riyaj.shamsudeen@xxxxxxxxx" <riyaj.shamsudeen@xxxxxxxxx>, "Mark.Bobak@xxxxxxxxxxxx" <Mark.Bobak@xxxxxxxxxxxx>, William Wagman <wjwagman@xxxxxxxxxxx>
- Date: Wed, 1 Apr 2009 14:18:10 +0300
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.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref332
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Riyaj Shamsudeen
Sent: Wednesday, April 01, 2009 6:24 AM
To: Mark.Bobak@xxxxxxxxxxxx
Cc: greg@xxxxxxxxxxxxxxxxxx; William Wagman; oracle-l@xxxxxxxxxxxxx
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):
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@xxxxxxxxxxxx<mailto:Mark.Bobak@xxxxxxxxxxxx>> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On Behalf
Of Greg Rahn [greg@xxxxxxxxxxxxxxxxxx<mailto:greg@xxxxxxxxxxxxxxxxxx>]
Sent: Tuesday, March 31, 2009 8:26 PM
To: William Wagman
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxx<mailto:wjwagman@xxxxxxxxxxx>> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
