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

Are there any indexes on the table, and
if so are they local or partitioned.

Your notes don't include any comment about what happens on the second hash join in the UNION ALL - does this take place before
any inserts, or do you

    hash, insert, hash, insert ?

Where are you getting the figures from about sorting ? Is this from v$sesstat rows sorted figures, v$sort_usage labelling temp segments, or are you just assuming that the temp segments in use are sort segments.

Two features that may be relevant:
a) When data moves from one set of parallel

    slaves to another, it does so through "table queues".     If it is not possible for the target slaves to execute     as the "table queues" are filled, then the "table queues"     are dumped to sort segments. This usually happens in     a multi-layer join/group/order query with only two     sets of slaves - it happens that the first set of slaves     is still performing an operation when the second set     of slaves wants to feed results to a third (recycled first)     set of slaves.

b) For an insert /*+ append */, oracle adds data above

    the high water mark on the table, then sorts the data     for each existing index before inserting it into the indexes,     as this may allow multiple index entries per leaf block,     which reduces the requirement for undo and redo on the     indexes. This may be where the sorting is coming from.     I don't know why you wouldn't see this on the non-parallel DML,     but (as your surmise) perhaps the operation works differently     when you do a serial /*+ append */ into a partitioned table.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

March 2004 Hotsos Symposium - The Burden of Proof
  Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar


"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message
news:qore40ttumu914c4cr3li35jr2hv179i9u_at_4ax.com...


> 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.

>