Re: CBO and connect by prior

> "Jamadagni, Rajendra" wrote:> > Hi all,> > one of my developers sent me this query ...> > SELECT s.show_number> FROM SHOWS s, APF_SHOWS h> WHERE h.spca_cat = s.spca_cat> AND h.spt_pgm_code = s.spt_pgm_code> AND h.show_id = s.ID> AND NVL(h.show_part,'~') = NVL(s.show_part,'~')> AND h.flt_id IN --(91681)> (SELECT f.flt_id> FROM FLIGHT_DATES f> START WITH f.flt_id = :b1> CONNECT BY PRIOR f.flt_id = f.parent_flt_id)> /> > complaining that it takes a long time ...> > Flight_dates has 76496 rows, shows has 305642 rows and apf_shows has> 310542 rows. All tables are analyzed with dbms_stats at 10% estimate.> The developers tells me that the sub_query by itself is very fast and> will at most return 5 rows. If I hardcode the IN clause, the query> returns very fast.> > Any ideas on how to influence the CBO so that it would *know* that the> sub-query will return a small number of rows?>

I would give a try to the subquery coming first as an inline-view in the
FROM clause, with ORDERED and possibly whatever it takes to prevent
Oracle from recombining everything. Or possibly better still, I would
try WITH, which seems to me a strong inducement to process the CONNECT
BY before anything else.

--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: sfaroult_at_oriole.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).