Re: Insert /* +append */ always ?

In article <adcsho$jjl$1_at_violet.singnet.com.sg>, "Hemant says...
>
>
>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 ?
>

CTAS and INSERT /*+ append */ write directly to the database blocks, bypassing the buffer cache.

What that means is that the data need no be protected by REDO in noarchivelog mode. We don't need to redo the operation since the operation is done directly to disk. Since media recovery is not possible in noarch mode -- no redo is needed (we don't need the redo for instance recover in these cases since the blocks are already safely on disk)

In archivelog mode, we do need the redo and the operations would have to be performed on NOLOGGING tables in order to bypass redo generation....

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

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp