Why does Oracle do Merge Join Cartesian when it causes execution to go from 1 min to 8 hours?

using Oracle 9.2.0.3 on Solaris 8 on Sun Server E15 with 10 CPUS 4 gig ram 2 TB disk

we have three tables

tableA
10k rows

tableb
86 million

tableC
100k

we do following qurey

select tableA.col1, tableB.Col1, tableC.col1 from
A, B, C
where
tableA.co1 = tableB.col1 and tableB.col2 = tableC.col1

this completes in 39 secs.

we then add one more where conditon we need two columns to be equal in tableA - and tableA.col1 = tableA.col4.

this takes 8 hours to run

plan for query for 39 secs (note that we are able to use the main index on tableB to get all cols so no need to access table index is 7.5 gig but we do a range scan

SELECT STATEMENT Optimizer Mode=CHOOSE          1               86             
          
  SORT GROUP BY         1       100     86
    HASH JOIN           1       100     83
      NESTED LOOPS              1       79      79 
        TABLE ACCESS FULL       TRST_WORK       1       25      56
        INDEX RANGE SCAN        IND_TREX_ACTUALS        2       108     23
      INDEX FAST FULL SCAN      IND_A7_DEPT     1 K     21 K    3              
          

when we add tableA.col1 = tableA.col4. it goes from 39 secs to 8 hours note plan now has a Merge Join Cartesian

SELECT STATEMENT Optimizer Mode=CHOOSE          1               85             
           SORT GROUP BY         1       100     85 
    NESTED LOOPS                1       100     82
      MERGE JOIN CARTESIAN              1       46      59 
        TABLE ACCESS FULL       TRST_WORK       1       25      56
        BUFFER SORT             1       21      3 
          INDEX FAST FULL SCAN  IND_A7_DEPT     1       21      3
      INDEX RANGE SCAN  IND_TREX_ACTUALS        1       54      23

tables were fully analysed with DBMS_STATS, etc but nothing changed

we finally added an Ordered Hint and that changed the plan to the good one.

why is this happening?? why is Oracle fooled so easily?

Is this bug: 3444115 CBO may choose inappropriate CARTESIAN join?

or is it just nature of the beast?

please help. we want to get away from using hints Received on Fri Feb 23 2007 - 21:17:01 CST