Re: Suboptimal query plan with connect by

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Mon, 29 Oct 2018 16:01:40 +0100
Message-ID: <753e2853-a177-c788-64fb-074326e4a1d5_at_gmail.com>



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:
> 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
Received on Mon Oct 29 2018 - 16:01:40 CET

Original text of this message