bad execution plan

JOE TESTA Tue, 30 Apr 2002 09:30:57 -0700

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:

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:

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:

I'm looking for any ideas what the heck the CBO is doing or is this some bug of some sorts?

thanks, joe

bad execution plan

Reply via email to