Re: Insert /* +append */ always ?

Thomas Kyte wrote:

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

Hey Tom, thanks for teh examples. It really helps drive home the point.

>
>
>
>
>
>>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
>
Received on Mon May 06 2002 - 22:45:43 CDT