Re: Why would an Insert /*+ append */ be slower than a straight insert?

rgaffuri_at_cox.net said...
> Here is the situation. Im testing a load process and I need to show
> results between the old method and a newer method to management.
>
> Here are the details.
> I did an
>
> Insert /*+ append */ into Master
> select * from stage;
>
> Also did it without append and the regular insert was about 40%
> faster. Here are the details.
>
> Oracle 8.1.7
> Solaris v5.8
> Each table in a seperate tablespace
> Same I/O device for everything
> Both tablespaces are taken offline to flush the DB_BUFFER_CACHE
> ALTER SYSTEM FLUSH SHARED_POOL; issued as well.
> 2.5m rows inserted.
> Master table is truncated between loads
> There is a primary key with 7 columns that is enabled(yes I know
> append is faster with disabled, but Im testing all possibilities)
> Both TABLESPACSE are set to NOLOGGING
> Both Tables are set to NOLOGGING.
> Only one session is active on the server. Nothing else is going on
>
> any ideas? Id assume at worst Append would be the same speed?
>

You might want to check out this Tom Kyte article that talks about one scenario that will cause +append to be long (will wrap):

http://asktom.oracle.com/pls/ask/f?p=4950:8:1090762::NO::F4950_P8 _DISPLAYID,F4950_P8_CRITERIA:3224814814761,

-- 
/Karsten
DBA > retired > DBA