Re: Why does Oracle sort hashjoin output before parallel insert?

On Thu, 04 Mar 2004 19:02:12 +0100, Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl> wrote:

>Hi,
>
>I've got the following statement (Oracle 8.1.7):
>
>insert /*+ append */ into outtable
>select [union all of 2 hash joins of 2 tables each]
>
>(I'm actually performing a refresh of a materialized view: this is the
>statement that is performed)
>
>The select is done in parallel. Outtable is partitioned.
>
>Oracle reads the first table of a hash join into hash memory and into
>hash segments in temp. What happens next depends on parallel dml being
>disabled or enabled.
>
>If parallel dml is disabled:
>as soon as Oracle starts reading the second table, records that can be
>joined are written to outtable. Other records are written to the hash
>segments en joined later and then written serially to outttable. No
>sort is performed. This is expected behaviour as far as I'm concerned.
>
>If parallel dml is enabled:
>records that are joined are written into sort segments in temp. All
>the output of the hash join is first sorted and after that written to
>outtable in parallel.
>
>My question: why are records sorted in the second case? Has it got to
>do with the partitioning of outtable? I hoped to achieve a shorter
>elapsed time for the parallel insert, but the sort counteracts this.
>
>Jaap.

I still don't know why Oracle behaves the way it does, but I did find the pattern.

With a simple select, join-output is written directly to table if the degree of parallelism for the insert is the same as that for the select (including both serial). If they differ in degree, join-output is written to a sort segment. After all join-output is written to this sort segment it is written to table.

With a select with a UNION ALL on the other hand join-output is only written directly to table if both the insert and the select are serial. In all other cases sort segment are used for intermediate storage. The difference with a simple select is therefore in the case of a parallel select and a parallel insert.

As stated in another posting in this thread no sorting is taken place.  

Jaap. Received on Wed Mar 17 2004 - 14:18:45 CST