> "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).
Received on Mon May 19 2003 - 14:16:48 CDT
