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

On Feb 24, 2:09 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Mistton" <mist..._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- Hide quoted text -

Page 417 of the "Cost-Based Oracle Fundamentals" book seems to provide the clue as to the source of the Cartesian merge join. Loosely paraphrased: The single-row table rule comes into play when Oracle tries to determine the best order in which to join tables. If the cost-based optimizer predicts that the conditions placed on a table will result in a single row being returned from the table, that table becomes the starting point (first table) of the join, and will not move from that position, even if it results in a Cartesian merge join when joined to the other tables. The LEADING() hint can be used to force a table as the starting point of the join, which will over-rule the single-row table rule.

The above seems to explain a lot. Side note: What happens if the statistics are not completely up to date? On Sunday when the tables and indexes were last analyzed, the table had exactly one row. On Monday 400 rows were inserted into the table. A query is then executed that joins multiple tables together, including the table that now has 401 rows - the 401 row table is likely selected as the starting point of the join as Oracle's statistics still indicate that a single row will be returned, and the resulting Cartesian merge join becomes very costly and time consuming to execute. The ORDERED hint can be used to correct this problem, although it likely takes a bit more preparation to use than the LEADING() hint.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Feb 25 2007 - 10:13:53 CST