| 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
/*+ PUSH_SUBQ */ ???
-----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
| 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 |Received on Mon May 19 2003 - 12:21:59 CDT
----------------------------------------------------------------------------
-- | 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: Chelur, Jayadas {PBSG} INET: jayadas.chelur_at_pepsi.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).
 |
 |