Re: Insert /* +append */ always ?

In article <ud8onhkf0meg1d_at_corp.supernews.com>, "Steve says...
>
>No.
>The append hint does not generate redo log information.

Sure it does! It is 100% safe. It is "undo" that it skips. It'll skip REDO if

o you are no archivelog mode (doesn't matter in this case, no media recovery   anyhow)

o your table is nologging

consider (in an archivelog mode database)

ops$tkyte_at_ORA8I.WORLD> create table t as select * from all_objects where 1=0;

Table created.

ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> set autotrace on statistics
ops$tkyte_at_ORA8I.WORLD> insert /*+ append */ into t select * from all_objects;

34950 rows created.

Statistics

        643  recursive calls
        119  db block gets
     476174  consistent gets
        270  physical reads
    3895776  redo size     <<<<<<<<<<<<<<<<=========================
        991  bytes sent via SQL*Net to client
        827  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
      34950  rows processed

ops$tkyte_at_ORA8I.WORLD> drop table t;

Table dropped.

ops$tkyte_at_ORA8I.WORLD> create table t NOLOGGING as select * from all_objects where 1=0;

Table created.

ops$tkyte_at_ORA8I.WORLD> insert /*+ append */ into t select * from all_objects;

34950 rows created.

Statistics

        151  recursive calls
        116  db block gets
     476062  consistent gets
         24  physical reads
      10208  redo size   <<<<<<<<<<<<<==============================
        994  bytes sent via SQL*Net to client
        827  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      34950  rows processed

>So the only time you should use it is when you can afford
>to loose the data in the table.

AND the table is NOLOGGING needs be added here. APPEND does not by itself bypass redo -- undo, and the related redo for the undo, YES. REDO -- no.

In NOarchivelog -- append bypasses redo, but that doesn't matter.

> Once your next hot or cold
>is taken than you off course are no longer at risk.
>
>So if you are loading right before a backup the risk is minimal. The longer
>you wait the bigger the risk.
>
>Also you need to be aware that when you use the append hint the data is
>inserted beginning at the high water mark of the table. So you have just
>deleted a bunch of data those blocks will not be reused. If the tables was
>just truncated than it will start loading at the beginning.
>

I don't understand the "so you have just deleted a bunch of data those blocks will not be reused". I think you were trying to say "the direct path insert will not reuse blocks that are on the freelist"

>Hope this helps.
>
>Stephen C. Ashmore
>Brainbench MVP for Oracle Administration
>http://www.brainbench.com
>
>Author of 'So You Want to be an Oracle DBA?'
>
>"R Chin" <rchin_at_panix.com> wrote in message
>news:ab1l09$94b$1_at_reader1.panix.com...
>> Should I ALWAYS do
>> Insert /* +append */ into select .....
>> as opposed to
>> Insert into select...... ???
>>
>> Under what circumstances should I NOT use the hint ?
>>
>> Thanks
>>
>> Rob
>>
>>
>>
>
>

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