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

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

RE: CBO and connect by prior

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 19 May 2003 10:56:50 -0800
Message-ID: <F001.0059CCB0.20030519105650@fatcity.com>


What about creating a function parallel_enable deterministic that takes two inputs: (:b1 and :b2) returns 1 if exists and 0 if not exist  

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 1 = func (:b1 , :b2 )  

Waleed    

-----Original Message-----
Sent: Monday, May 19, 2003 11:39 AM
To: 'ORACLE-L_at_fatcity.com'

Does this work for you:  

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 exists
      (SELECT 1
       FROM FLIGHT_DATES f 
       where f.flt_id = h.flt_id 
       START WITH f.flt_id = :b1 
       CONNECT BY PRIOR f.flt_id = f.parent_flt_id) 
 
 

Waleed  

-----Original Message-----
Sent: Monday, May 19, 2003 11:52 AM
To: Multiple recipients of list ORACLE-L

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: Khedr, Waleed INET: Waleed.Khedr_at_FMR.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 - 13:56:50 CDT

Original text of this message

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