Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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 CorpReceived on Mon May 06 2002 - 20:19:45 CDT