Re: Re: Suboptimal query plan with connect by

From: <l.flatz_at_bluewin.ch>
Date: Mon, 29 Oct 2018 16:05:45 +0100 (CET)
Message-ID: <359734963.37773.1540825545657.JavaMail.webmail_at_bluewin.ch>





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

Original text of this message