I'd guess the optimizer is applying transitivity
to use your constant against all three tables
at the optimise stage, and something about the
specific value is fooling it.
What are the low_value and high_value
columns in user_tab_columns for the
three columns in the WHERE clause ?
Is EV000154 apparently outside the range
on any of them ? And why are columns that
are clearly supposed to be holding numeric
values being compared with something that
is not ? joke
If you include the CARDINALITY column from
the execution plan, does it give you any ideas -
such as CARD=1 anywhere early on ?
My guess would be that Oracle has switched
to indexed access and merge joins because
it has estimated a zero row return from
STAGING.BECONS
STAGING.BECATD
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 30 April 2002 17:57
AIX, oracle 8.1.7.2, all 3 tables compute statistics, first_rows
optimizer_mode
a simple 3 table join, joined on the appropriate columns, works fine.
add a fixed constant to the mix and poof, 2 merge-join cartesian.
Here is first query w/o the fixed value:
select
T1.EVENT# c1,
count(distinct T1.CONCO#) c2,
T2.EVDESC c3,
T3.CATTYP c4 ,
count(distinct T3.CATCO#) c5
from STAGING.BECONS T1,
STAGING.EVMAST T2,
STAGING.BECATD T3
where
T1.EVENT# = T2.EVMBRN and
T1.EVENT# = T3.EVENT#
group by T1.EVENT#, T2.EVDESC, T3.CATTYP
order by 1 asc, 3 asc, 4 asc;
Plan:
| SELECT STATEMENT |
| SORT GROUP BY |
| HASH JOIN|
|HASH JOIN |
| TABLE ACCESS FULL |EVMAST
| TABLE ACCESS FULL |BECONS
|TABLE ACCESS FULL |BECATD
Now add one line with a hardcoded value and it appears that oracle is
totally ignoring the rest of the where clause(proven later):
select
T1.EVENT# c1,
count(distinct T1.CONCO#) c2,
T2.EVDESC c3,
T3.CATTYP c4 ,
count(distinct T3.CATCO#) c5
from STAGING.BECONS T1,
STAGING.EVMAST T2,
STAGING.BECATD T3
where
T1.EVENT# = T2.EVMBRN and
T1.EVENT# = T3.EVENT#
and T1.EVENT#='EV000154'
group by T1.EVENT#, T2.EVDESC, T3.CATTYP
order by 1 asc, 3 asc, 4 asc;
plan:
| SELECT STATEMENT |
| SORT GROUP BY |
| MERGE JOIN CARTESIAN |
|MERGE JOIN CARTESIAN|
| TABLE ACCESS FULL |EVMAST
| SORT JOIN |
| TABLE ACCESS BY INDEX ROWID |BECONS
| INDEX RANGE SCAN |IDX01_BECONS
|SORT JOIN |
| TABLE ACCESS BY INDEX ROWID|BECATD
| INDEX RANGE SCAN |INDX01_BECATD
Now proven here is if i leave out the joins altogether, i get the
close the the same execution plan as above :
select
T1.EVENT# c1,
count(distinct T1.CONCO#) c2,
T2.EVDESC c3,
T3.CATTYP c4 ,
count(distinct T3.CATCO#) c5
from STAGING.BECONS T1,
STAGING.EVMAST T2,
STAGING.BECATD T3
where
T1.EVENT#='EV000154'
group by T1.EVENT#, T2.EVDESC, T3.CATTYP
order by 1 asc, 3 asc, 4 asc;
Plan:
| SELECT STATEMENT |
| SORT GROUP BY |
| MERGE JOIN CARTESIAN |
|MERGE JOIN CARTESIAN|
| TABLE ACCESS BY INDEX ROWID|BECONS
| INDEX RANGE SCAN |IDX01_BECONS
| SORT JOIN |
| TABLE ACCESS FULL |EVMAST
|SORT JOIN |
| TABLE ACCESS FULL |BECATD
I'm looking for any ideas what the heck the CBO is doing or is this
some bug of some sorts?
thanks, joe
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
