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

EscVector wrote:
> 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?

Offline by meaning I kill the temp file to make sure it can't be used... Received on Wed Nov 22 2006 - 09:21:52 CST