Re: Why does Oracle buffer the result from the select for a parallel INSERT INTO SELECT FROM?

R. Schierbeek wrote:
> Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl> schreef in bericht
> > Hi,
> > I'm on Oracle 9.2.0.5, Open VMS.
> >
> > I perform an
> > INSERT INTO target
> > SELECT * FROM source
> >
> > If I do this noparallel, the result from the select is immediately inserted into the target.
> >
> > If I do this in parallel, the result of the selects from a slave set
> > are first buffered in TEMP. After this is done, a slave set starts to
> > insert the contents from the buffers into the target.
> >
> > Why this timeconsuming buffering? Can anything be done?
> >
> > While I am writing this, I wonder: I let Oracle decide which degree of
> > parallellization to use. Maybe buffering only takes places if the
> > degree differs between SELECT and INSERT? (I should test myself, but
> > don't have the opportunity right now).
> > Regards, Jaap.
>
> Well Jaap,
> If you insert less then 100.000 rows a parallel insert might not be faster then
> a normal insert /*+APPEND */. Check out the APPEND hint.
>
> If you still want to do the parallel insert without TEMP usage then
> drop the indexes on the target table. Oracle first inserts the table data;
> then does some sorting on the new index data and rebuilds them.
> No index > no sorts needed.
>
> Cheers,
> Roelof Schierbeek; DBAb

So if I offline my tempspace and run a parallel query it will work as long as the table being queried has not indexes? Received on Wed Nov 22 2006 - 09:18:23 CST