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

From: <zejeanmi_at_gmail.com>
Date: 4 Jan 2007 09:03:46 -0800
Message-ID: <1167930226.378369.69890@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?

I tried some hints with no luck...

Any clue ?

Thanks a lot in advance

Jean-Michel Received on Thu Jan 04 2007 - 11:03:46 CST