Path: news.f.de.plusline.net!news-fra1.dfn.de!news.tele.dk!feed118.news.tele.dk!postnews.google.com!s34g2000cwa.googlegroups.com!not-for-mail From: zejeanmi@gmail.com Newsgroups: comp.databases.oracle.server Subject: Connect by START WITH+ IN subquery - query optimization - help ! Date: 4 Jan 2007 09:03:46 -0800 Organization: http://groups.google.com Lines: 75 Message-ID: <1167930226.378369.69890@s34g2000cwa.googlegroups.com> NNTP-Posting-Host: 212.203.82.128 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1167930232 6691 127.0.0.1 (4 Jan 2007 17:03:52 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 4 Jan 2007 17:03:52 +0000 (UTC) User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.8.0.9) Gecko/20061206 Firefox/1.5.0.9,gzip(gfe),gzip(gfe) X-HTTP-Via: 1.1 PROXYTST2, 1.1 PROXYTST2:8080 (IWSS) Complaints-To: groups-abuse@google.com Injection-Info: s34g2000cwa.googlegroups.com; posting-host=212.203.82.128; posting-account=KXsC6g0AAABavmZGVNWgkSDN6kOtvB01 Xref: news.f.de.plusline.net comp.databases.oracle.server:191720 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