Re: 11g: select with union all from two different table function not parallel

From: Frank Bergemann <FBergemann_at_web.de>
Date: Wed, 22 Feb 2012 00:20:02 -0800 (PST)
Message-ID: <9b7dc035-9fd4-4b45-a6fb-fb2b60c7aa56_at_q12g2000yqg.googlegroups.com>

On Feb 20, 10:39 pm, Randolf Geist <i..._at_sqltools-plusplus.org> wrote:
> On 20 Feb., 13:50, Frank Bergemann <FBergem..._at_web.de> wrote:
>
> > --------------------------------------------------------------------------------------------------------
> > insert into ...
> > select * from (
> >     select * from TableFunc1(...)
> >     union all
> >     select * from TableFunc2(...)
> > );
>
> > ... does not start any TableFunc2 instance before all TableFunc1
> > instances have finished.
>
> > I thought 'union all' will not sequentialize the two queries(?!)
> > Or do i miss something else to make it work in parallel?
>
> Your assumption is wrong as you've proved to yourself already. The
> UNION operator, like many other operators, e.g. a HASH JOIN, starts
> one child operation after the other (in this case exactly once). As
> far as I know Oracle is not capable of what you seem to have in mind
> respectively that's not the way Oracle executes an execution plan.
>
> As you've already pointed out in the OTN thread you would need to
> perform such a parallelism yourself, for example by spawning multiple
> sessions that each perform a parallel execution of your two table
> functions.
>
> Hope this helps,
> Randolf

But what about http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21547067945222 Shouldn't 'union all' allow parallel execution?

regards,
Frank Received on Wed Feb 22 2012 - 02:20:02 CST