Re: 11g: select with union all from two different table function not parallel
From: Randolf Geist <info_at_sqltools-plusplus.org>
Date: Mon, 20 Feb 2012 13:39:53 -0800 (PST)
Message-ID: <7808e77b-96a6-42e1-98fc-7637534626e8_at_bs8g2000vbb.googlegroups.com>
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?
Date: Mon, 20 Feb 2012 13:39:53 -0800 (PST)
Message-ID: <7808e77b-96a6-42e1-98fc-7637534626e8_at_bs8g2000vbb.googlegroups.com>
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
Received on Mon Feb 20 2012 - 15:39:53 CST