Re: Insert /* +append */ always ?

In article <1020705640.12845.0.nnrp-07.9e984b29_at_news.demon.co.uk>, "Jonathan says...
>
>
>I guess it's probably worth flagging the point that
>any beneftis you get from the APPEND hint
>related only to the table, and not to any indexes
>on the table.
>

well yes and no. I would say it like this

"MUCH of the benefit you get will be on non-indexed tables, indexed tables will generate some, but less -- sometimes significantly less, redo in general due to their bulk index maintenance with append"

Consider:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects; Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create index t_idx1 on t(object_id); Index created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace on statistics ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;

22921 rows created.

Statistics

        131  recursive calls
       1447  db block gets
      74755  consistent gets
         51  physical reads
    1651220  redo size
        997  bytes sent via SQL*Net to client
        827  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      22921  rows processed

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> rollback;

Rollback complete.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t select * from all_objects;

22921 rows created.

Statistics

          7  recursive calls
      34973  db block gets
      75106  consistent gets
        144  physical reads
    6240872  redo size
       1013  bytes sent via SQL*Net to client
        813  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      22921  rows processed

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

Compare:

    1651220 redo size
    6240872 redo size

(this was done in a noarchivelog mode database btw -- table would have to be nologging in archivelog mode)

The bulk index operation at the end can save quite a bit...

Similar results are obtained going the other way (insert followed by insert append)

>Oracle can "cheat" on tables by using blocks
>above the HWM for the table to hold the new
>data, but it has to put the index entries into
>the correct index leaf blocks - so these changes
>have to be undo-able in the normal fashion.
>
>--
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Author of:
>Practical Oracle 8i: Building Efficient Databases
>
>Next Seminar - Australia - July/August
>http://www.jlcomp.demon.co.uk/seminar.html
>
>Host to The Co-Operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>Thomas Kyte wrote in message ...
>>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
>>
>
>
>

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