Re: Insert /* +append */ always ?

In article <1020768602.15108.0.nnrp-07.9e984b29_at_news.demon.co.uk>, "Jonathan says...
>
>
>I won't argue with the principle, which
>is totally correct, but I think you may
>have given too rosy an outlook to using
>insert /*+ append */
>
>Notes in-line.
>

note to self, never contradict JPL ;) I thought loooonnngggg and hard before posting this. LOL

What you say is true -- to answer the question in the subject "insert append always?" the answer is a definitive and resounding NO. During a mass bulk insert -- sure (try to use nologging, coordinate with the DBA so they can backup properly). For the day to day -- no way.

Thanks for the followup.... Good as always.

(yes, I had to look for an example or two to get such a rosy outlook -- just couldn't resist following up a statement like:

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

with a contra example ;) Everytime I speak in absolutes, it seems someone finds a contra case.... I should have been more verbose however and should have done the inserts with/without the indexes to see the real difference. As you pointed out -- the 4.4 meg is partially the table. So, I ran this instead:

drop table t;
create table t as select * from all_objects; set autotrace on statistics;
insert into t select * from all_objects; rollback;
insert /*+ append */ into t select * from all_objects; rollback;
create index t_idx1 on t(object_id);
insert into t select * from all_objects; rollback;
insert /*+ append */ into t select * from all_objects; rollback;
set autotrace off

and the results are:

    2572664 redo size

       7304 redo size
    7114216 redo size
     694948 redo size

this time...

So, the table adds about 2.5meg of redo -- meaning we saved about 2meg on the index.

It is somewhat interesting to note that if you run the inserts in the other direction (append and then NON-append), the numbers are:

       7344 redo size
    2565144 redo size
    1649004 redo size
    6201836 redo size

which align with my example below (that was the order I did them in originally)... That would be the subject of another thread tho ;)

>
>--
>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 ...
>>
>>"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"
>>
>
>
>True, I had overlooked this feature - although for relatively small inserts
>into existing large data sets the benefit is not necessarily all that great.
>As you indicate, it needs to be able to leverage the feature of updating
>index blocks with multiple rows
>
>I am sure that your choice of example was made to highlight the
>point you were making, but I think it could be mis-interpreted in
>too general, and optimistic a light.
>
>
>>
>>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
>>
>
>So we have effectively a nologging table, and an unsorted
>insert append. Redo size 1.6MB, and a statistic not reported
>of "rows sorted" = 22921.
>
>
>>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
>>
>
>
>Effectively a logged table with an unsorted insert
>Redo size 6.2 MB.
>
>Total saving 4.7MB.
>
>Of this, I would estimate that ca. 2.2 MB would be
>because the table insert was not logged, and the
>remaining 2.4 MB was because of the bulk insert
>benefit.
>
>However, the bulk insert benefit in this case is
>huge because you are doubling the size of an
>index by inserting alternate new rows.
>Since the algorithm allows for one redo record
>per index block, I estimate that on a 4K block
>size you would get about 220 rows per index,
>and therefore your insert is getting the benefit of
>110 index rows per redo record (i.e. redo block),
>rather than the opposite extreme of one redo record
>per index row. (You also happen to have very small
>index entries - so the redo vector overhead is a large
>multiple of the redo data).
>
>
>In this type of situation, and assuming that you
>didn't want to lose the benefit of logging the table,
>I think you would find that:
> insert into t
> select * from all_objects
> order by object_id
>would be virtually identical in cost to doing an
>insert /*+ append */ without the order by on
>a logged table, viz ca. 3.85 MB, made up from
> 1.65 MB of index logging
> 2.2 MB of table logging
>
>Results, would of course vary with the number
>and usability of blocks on the free lists.
>
>
>Bottom line - insert append is likely to be more
>efficient (though there are side-effects to check),
>but the performance benefits won't necessarily be
>as dramatic as Tom's demonstration.
>
>
>
>
>
>
>

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