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

"Mistton" <mistonl_at_mail.com> wrote in message news:45dfadb5$0$69369$bb4e3ad8_at_newscene.com...
> 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

Cost Based Oracle Fundamentals Chapter 10.

<quote>

    To apply this predicate [t2.join1 = t1.join1], we look at it in two different ways, namely:

        t2.join1 = :unknown_value     or

        :unknown_value = t1.join1
    Then we simply pick whichever one of these two conditions is the most selective
<end quote>

The comment is made in relation to join predicates - but applies equally well to your predicate tableA.col1 = tableA.col4.

It would help if you had used a proper execution plan generator (dbms_xplan) and used the real table names, but I guess your tableA is A7_DEPT below. As you can see, the extra predicate has dropped the estimated cardinality of the fast full scan from 1K to 1. I guess at least one of those columns has a selectivity of 1 in 1,000

> INDEX FAST FULL SCAN IND_A7_DEPT 1 K 21 K 3

> INDEX FAST FULL SCAN IND_A7_DEPT 1 21 3
This in itself may or may not be a good idea - depending on the accuracy of the cardinality estimates of the other table in the join, but at least one side effect is the escalation in the number of times the subsequent range scan will take place.

Other than hinting, I don't think there's anything sensible you can do about this that won't run the risk of massive side-effects elsewhere.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html