Re: Insert /* +append */ always ?

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.

>