Re: Connect by START WITH+ IN subquery - query optimization - help !

<zejeanmi_at_gmail.com> wrote in message
news:1167930226.378369.69890_at_s34g2000cwa.googlegroups.com...
> Hello,
>
> I have a problem with this query that is painfully slow :
>
> =====================================================================
> SELECT OPM_N_ID FROM RAFC_ADM.AFC_T_OPERATION_METIER
> START WITH OPM_N_ID IN
> (
> SELECT OPM_N_ID FROM RAFC_ADM.AFC_T_OPERATION_METIER x
> START WITH x.OPM_N_ID IN (4846)
> CONNECT BY ((PRIOR x.OPM_MERE_OPM_N_ID = x.OPM_N_ID)
> OR (PRIOR x.OPM_ANNULEE_OPM_N_ID = x.OPM_N_ID))
> )
> CONNECT BY ((PRIOR OPM_N_ID = OPM_MERE_OPM_N_ID) OR (PRIOR OPM_N_ID =
> OPM_ANNULEE_OPM_N_ID))
> ------------------------------------------------------------------------------------------
> the explain plan
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 15 K 31
>
> CONNECT BY WITHOUT FILTERING
> TABLE ACCESS FULL RAFC_ADM.AFC_T_OPERATION_METIER 15 K 106 K 31
> FILTER
> CONNECT BY WITHOUT FILTERING
> TABLE ACCESS FULL RAFC_ADM.AFC_T_OPERATION_METIER 15 K 106 K 31
> =====================================================================
>
>
> Alone, the SELECT ... CONNECT BY in the IN subquery is quite fast and
> retrieve only 2 values (lines) : 4846, 4845.
>
> If i replace the IN subquery with simple select from dual, the whole
> query becomes very fast too :
>
> =====================================================================
> SELECT OPM_N_ID FROM RAFC_ADM.AFC_T_OPERATION_METIER
> START WITH OPM_N_ID IN
> (
> SELECT 4846 FROM dual
> UNION ALL
> SELECT 4845 FROM dual
> )
> CONNECT BY ((PRIOR OPM_N_ID = OPM_MERE_OPM_N_ID) OR (PRIOR OPM_N_ID =
> OPM_ANNULEE_OPM_N_ID))
> ------------------------------------------------------------------------------------------
> the explain plan
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 15 K 31
>
> CONNECT BY WITHOUT FILTERING
> TABLE ACCESS FULL RAFC_ADM.AFC_T_OPERATION_METIER 15 K 106 K 31
> UNION-ALL
> FILTER
> TABLE ACCESS FULL SYS.DUAL 8 K 11
> FILTER
> TABLE ACCESS FULL SYS.DUAL 8 K 11
> =====================================================================
>
>
> So I would like oracle to evaluate the IN subquery once (=>retrieve the
> 2 lines), carry on with the main connect by. I guess it would be fast.
>
> Do you have any idea how to force the START WITH IN subquery to be
> completely evaluated first?
>

It would be helpful if you produced a proper execution plan using dbms_xplan, which is a particularly convenient way of getting most of the plan detail.

As a quick and dirty fix, and assuming you are on 9i, , you might try

with part1 as (

    select /*+ materialize */
 SELECT OPM_N_ID FROM RAFC_ADM.AFC_T_OPERATION_METIER x

    START WITH x.OPM_N_ID IN (4846)
 CONNECT BY ((PRIOR x.OPM_MERE_OPM_N_ID = x.OPM_N_ID)  OR (PRIOR x.OPM_ANNULEE_OPM_N_ID = x.OPM_N_ID)) )
SELECT OPM_N_ID FROM RAFC_ADM.AFC_T_OPERATION_METIER START WITH OPM_N_ID IN
     (select opm_n_id from part1)
 CONNECT BY ((PRIOR OPM_N_ID = OPM_MERE_OPM_N_ID) OR (PRIOR OPM_N_ID =  OPM_ANNULEE_OPM_N_ID)) Alternatively - did you try putting the /*+ no_merge */ hint into the inner query ?

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html