| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: CBO and connect by prior
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
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
(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
(SELECT f.flt_id
FROM FLIGHT_DATES f
START WITH f.flt_id = :b1
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
|* 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
--Received on Mon May 19 2003 - 13:56:50 CDT
| 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).
 |
 |