Re: Suboptimal query plan with connect by

Sounds like a good plan.

Regards

Lothar
Am 29.10.2018 um 16:09 schrieb Radoulov, Dimitre:
>
> We'll first try to divide the query in order to avoid the "OR"
> operator in the "connect by", run both queries in parallel and add a
> third "connect by" to correlate the resultsets after.
>
>
> Regards
> Dimitre
>
>
> On 29/10/2018 16:05, l.flatz_at_bluewin.ch wrote:
>> Hi,
>>
>> 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,
>>
>> 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