Re: Insert /* +append */ always ?

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:38:02 CDT