Re: Insert /* +append */ always ?

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.

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