Dunno, but it does. SQL Loader does the same sort of thing in direct mode.
Anyway: point is, Jonathan was right, and I was stupid. New 9i release 2
database not yet put into archivelog mode.
Regards
HJR
"Hemant K Chitale" <hkchital_nospam_at_singnet_nospam.com.sg> wrote in message
news:adcsho$jjl$1_at_violet.singnet.com.sg...
>
> Why should the archivelog mode (ARCHIVELOG or NOARCHIVELOG)
> make a difference to whether a transaction (e.g the CTAS
> you mention) goes into Redo or not ?
>
>
> Hemant K Chitale
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:1023004178.28510.0.nnrp-13.9e984b29_at_news.demon.co.uk...
> >
> > I suffered from the same delusion for a long time,
> > perhaps for exactly the same reason - and it is
> > even possible that something of this sort was
> > stated explicitly in the early releases of the feature.
> >
> > However, it is only the UNDO that is omitted,
> > and then only the UNDO on the table, not on
> > its indexes. The REDO is still generated.
> >
> > Of course, your example does suggest that
> > the REDO is not generated either, but
> > was your database in NOARCHIVELOG mode ?
> >
> > People often overlook the fact that Create Table
> > as Select is not logged if the database is in
> > NOARCHIVELOG mode, regardless of whether
> > the CTAS is LOGGING or NOLOGGING. I haven't
> > checked it, but I wouldn't be surprised if the same
> > applied to /*+ append */.
> >
> >
> > MYTH WARNING
> > MYTH WARNING
> > MYTH WARNING
> >
> > It is quite easy to 'prove' a point by doing an experiment
> > which looks okay, but doesn't quite cater for all the
> > necessary details.
> >
> > MYTH WARNING
> > MYTH WARNING
> > MYTH WARNING
> >
> >
> >
> > --
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Next Seminars
> > UK June / July
> > Australia July / August
> > http://www.jlcomp.demon.co.uk/seminar.html
> >
> > Howard J. Rogers wrote in message ...
> >
> > >
> > >True, somewhat to my surprise: I had assumed that the 'nologging'
> attribute
> > >needed to make an appearance for this to be the case, but the following
> > >demonstrates that not to be the case:
> > >
> > >SQL> create table blah as select * from emp;
> > >Table created.
> > >
> > >SQL> insert into blah select * from blah;
> > >14 rows created.
> > >
> > >Statistics
> > >----------------------------------------------------------
> > > 1200 redo size
> > >
> > >SQL> insert /*+ APPEND */ into blah select * from blah;
> > >28 rows created.
> > >
> > >Statistics
> > >----------------------------------------------------------
> > > 52 redo size
> > >
> > >(non-key statistics have been removed, obviously).
> > >
> > >Odd then that the Oracle training material always demonstrates the
direct
> > >load insert with the nologging attribute being used -when clearly it's
> > >utterly redundant.
> > >
> >
> >
> >
>
>
Received on Sun Jun 02 2002 - 05:46:04 CDT
