bad execution plan

From: JOE TESTA <JTESTA_at_longaberger.com>
Date: Tue, 30 Apr 2002 09:24:08 -0800
Message-ID: <F001.00453AEB.20020430092408@fatcity.com>

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: JOE TESTA
  INET: JTESTA_at_longaberger.com

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 - 12:24:08 CDT