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

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; DBA Received on Wed Nov 22 2006 - 07:45:17 CST