Re: bad execution plan

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 30 Apr 2002 11:13:52 -0800
Message-ID: <F001.00453D33.20020430111352@fatcity.com>

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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: 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 Tue Apr 30 2002 - 14:13:52 CDT