| 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
I suggested something on the similar lines, but the code is way too many
places (per developer). So, the easiest thing to do is change the way CBO
works.
That's easy.
Raj
-----Original Message-----
Sent: Monday, May 19, 2003 2:57 PM
To: Multiple recipients of list ORACLE-L
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
| 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 - 14:46:56 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: 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).
- text/plain attachment: ESPN_Disclaimer.txt
 |
 |