Re: Suboptimal query plan with connect by

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Mon, 29 Oct 2018 14:49:06 +0100
Message-ID: <bacb120e-4e3d-bfc8-6a17-6053ce8c7345_at_gmail.com>

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_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 - 14:49:06 CET