Re: Re: Suboptimal query plan with connect by
Date: Mon, 29 Oct 2018 16:05:45 +0100 (CET)
Message-ID: <359734963.37773.1540825545657.JavaMail.webmail_at_bluewin.ch>
Hi,
Hi,
with "_old_connect_by_enabled"=true the query still runs in
serial mode.
Thank you!
Regards
Dimitre
On 29/10/2018 15:30, Lothar Flatz
wrote:
thanks. The pipeline Table function will work in parallel. The efficiency depends a bit on how your hierarchies are constructed.
Regards
Lothar
----Ursprüngliche Nachricht----
Von : cichomitiko_at_gmail.com
Datum : 29/10/2018 - 16:01 (CET)
An : l.flatz_at_bluewin.ch, xt.and.r_at_gmail.com
Cc : oracle-l_at_freelists.org
Betreff : Re: Suboptimal query plan with connect by
Hi Sayan,
I think Dimitre should try the easiest thing first. alter
session set “_old_connect_by_enabled”=true;
It might be possible that the old algorithm can be parallelized
without further work.
The old algorithm is depth first, I guess that could help.
The pipe table function is a bit forced nut I guarantee parallel
processing. It must be adapted to the respective case. The outer
query (ref cursor) retrieves the start points (root rows).
If there is only one root, we need to retrieve the first level
under the root.
Of course it is possible to return every row, you just have to
adapt the inner query.
Regards
Lothar
Am 29.10.2018 um 14:37 schrieb Sayan Malakshinov:
Lothar,
But as I see your example just calculates a sum of
children rows, it doesn't return them. I wander hot it can
help return a tree?
Dimitre,
As far as I know "connect by" cannot be parallellized,
but of course optimizer can use parallel execution for
children row sources.
And sometimes we can optimize it using Recursive
subquery factoring clause, ie recursive WITH (it was
optimized in 12.2)
For example:
create
table th as
select
nullif(level-1,0) parent_id, level id, mod(level,2) x
from dual
connect by level<=1e5;
create
index th_idx2 on th(parent_id);
select/*+
parallel */ *
from th
connect by
parent_id = prior id
start with
parent_id=0
/
with v(id,
parent_id, x) as (
select
id,parent_id,x from th where parent_id=0
union all
select th.id,th.parent_id,th.x
from v,th where th.parent_id=v.id
)
select/*+
parallel */ *
from v
/
On Mon, Oct 29, 2018 at 4:24 PM Radoulov,
Dimitre <cichomitiko_at_gmail.com>
wrote:
On 29/10/2018 14:17, Lothar Flatz wrote:
> Hi,
>
> you could use this technique:
>
> https://oracleriddleblog.wordpress.com/2015/03/23/solution-navigate-many-shallow-hierachies-in-parallel/
>
> It will also show you an other hidden parameter. Make
sure the indexes
> are in place!
>
> Regards
>
> Lothar
Thank you Lothar!
I'll try your solution with the pipelined function.
Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l
--
Best regards,
Sayan Malakshinov
Oracle performance tuning
engineer
Oracle ACE Associate
http://orasql.org
-- -- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 29 2018 - 16:05:45 CET