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 <http://th.id>,th.parent_id,th.x from v,th where
> th.parent_id=v.id <http://v.id>
> )
> select/*+ parallel */ *
> from v
> /
>
>
> On Mon, Oct 29, 2018 at 4:24 PM Radoulov, Dimitre
> <cichomitiko_at_gmail.com <mailto: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-l