Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> CBO and connect by prior

CBO and connect by prior

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Mon, 19 May 2003 07:51:55 -0800
Message-ID: <F001.0059C8C9.20030519075155@fatcity.com>


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?

following are the explain plans and the script I used to generate them.

10:40:39 SQL> @1
Connected.
Enter value for b1: 91681
old 11: start with f.flt_id = &b1 connect by prior f.flt_id = f.parent_flt_id)
new 11: start with f.flt_id = 91681 connect by prior f.flt_id = f.parent_flt_id)

Explained.

PLAN_TABLE_OUTPUT




| Id | Operation | Name | Rows | Bytes
|TempSpc| Cost |



| 0 | SELECT STATEMENT | | 1 | 53
| | 1756 |
|* 1 | HASH JOIN SEMI | | 1 | 53
| | 1756 |
|* 2 | HASH JOIN | | 1 | 40
| 9560K| 1753 |
| 3 | TABLE ACCESS FULL | SHOWS | 305K|
5974K|       |   997 |

| 4 | INDEX FAST FULL SCAN | APFSHOW_PK | 311K|
6089K| | 271 |
| 5 | VIEW | VW_NSO_1 | 25499 |
323K| | 2 |
|* 6 | CONNECT BY WITH FILTERING | | |
| | |
| 7 | NESTED LOOPS | | |
| | |
|* 8 | INDEX UNIQUE SCAN | FLT_UN | 1 | 5
| | 1 |
| 9 | TABLE ACCESS BY USER ROWID | FLIGHT_DATES | |
| | |
| 10 | NESTED LOOPS | | |
| | |
| 11 | BUFFER SORT | | 25499 |
149K| | |
| 12 | CONNECT BY PUMP | | |
| | |
| 13 | TABLE ACCESS BY INDEX ROWID| FLIGHT_DATES | 25499 |
149K| | 2 |
|* 14 | INDEX RANGE SCAN | FLIGHT_PARENT_IDX | 1 |
| | 1 |
---------------------------------------------------------------------------- ------------------

Predicate Information (identified by operation id):


   1 - access("H"."FLT_ID"="VW_NSO_1"."$nso_col_1")    2 - access("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",'~')
   6 - filter("F"."FLT_ID"=91681)
   8 - access("F"."FLT_ID"=91681)
  14 - access("F"."PARENT_FLT_ID"=NULL)

Note: cpu costing is off

32 rows selected.

Enter value for b1: 91681
old 8: and h.flt_id in (&b1)
new 8: and h.flt_id in (91681)

Explained.

PLAN_TABLE_OUTPUT


--

| Id | Operation | Name | Rows | Bytes | Cost
|
---------------------------------------------------------------------------- --
| 0 | SELECT STATEMENT | | 1 | 40 | 52
|
| 1 | TABLE ACCESS BY INDEX ROWID| SHOWS | 1 | 20 | 3
|
| 2 | NESTED LOOPS | | 1 | 40 | 52
|
|* 3 | INDEX RANGE SCAN | APFSHOW_PK | 24 | 480 | 3
|
|* 4 | INDEX RANGE SCAN | SHOW_SPORT_UK | 1 | | 2
|
---------------------------------------------------------------------------- -- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("H"."FLT_ID"=91681) 4 - access("H"."SPCA_CAT"="S"."SPCA_CAT" AND "H"."SPT_PGM_CODE"="S"."SPT _PGM_CODE" AND "H"."SHOW_ID"="S"."ID" filter(NVL("H"."SHOW_PART",'~')=NVL("S"."SHOW_PART",'~')) Note: cpu costing is off 20 rows selected. Any ideas will be greatly appreciated. BTW we are on 9202 ... Thanks in advance Raj ---------------------------------------------------------------------------- ---- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.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 - 10:51:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US