Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why would an Insert /*+ append */ be slower than a straight insert?

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

From: Ryan <rgaffuri_at_cox.net>
Date: Fri, 23 May 2003 21:24:02 GMT
Message-ID: <Sjwza.63026$823.59217@news1.east.cox.net>

"Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message news:MPG.193800d52401e85d989794_at_news.la.sbcglobal.net...
> 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

thanks, but Im not inserting one row at a time. I have to figure out how to use sqltrace. I dont get why /*+append */ is slower. Received on Fri May 23 2003 - 16:24:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US