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

Home -> Community -> Usenet -> c.d.o.server -> Why does Oracle buffer the result from the select for a parallel INSERT INTO SELECT FROM?

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Wed, 22 Nov 2006 10:23:21 GMT
Message-ID: <456424d5.2668765@news.hetnet.nl>

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. Received on Wed Nov 22 2006 - 04:23:21 CST