Sayan,
yes, if I remove the "OR" in the "connect by" clause the query runs in
parallel (modified output):
...
CONNECT BY (
A.new_id = PRIOR A.id
AND A.other_id = PRIOR A.other_id
)
START WITH (
A.other_date IS NULL
OR A.other_date >= ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
)
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
E-Rows | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| |
|* 1 | FILTER | | | | | |
|* 2 | CONNECT BY NO FILTERING WITH START-WITH|
| | 92M| 3298K| 97M (1)|
| 3 | PX COORDINATOR | | | 73728 | 73728
| |
| 4 | PX SEND QC (RANDOM) | :TQ10000 |
1328K| | | |
| 5 | PX BLOCK ITERATOR | | 1328K|
| | |
|* 6 | TABLE ACCESS FULL | tname |
1328K| | | |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("A"."date1"<TO_DATE(' 2018-10-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')
AND ("A"."date2" IS NULL OR "A"."date2">=TO_DATE('
2018-09-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))))
2 - access("A"."new_id"=PRIOR NULL AND "A"."other_id"=PRIOR NULL)
filter(("A"."date2" IS NULL OR "A"."date2">=TO_DATE(' 2018-10-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
6 - access(:Z>=:Z AND :Z<=:Z)
Regards
Dimitre
On 29/10/2018 14:37, Sayan Malakshinov wrote:
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@xxxxxxxxx <mailto:cichomitiko@xxxxxxxxx>> 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
--
//www.freelists.org/webpage/oracle-l
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
