Why does Oracle sort hashjoin output before parallel insert?

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. Received on Thu Mar 04 2004 - 12:02:12 CST