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

On Feb 25, 2:05 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Charles Hooper" <hooperc2..._at_yahoo.com> wrote in message
> news:1172420033.089958.65020_at_j27g2000cwj.googlegroups.com...
> > 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.
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> Charles,
>
> The single-row case applies only when the table
> is logically __guaranteed__ to return one row based
> on the single-table access predicates
> e.g. there is an equality predicate on the primary key
> or (e.g.2) there is a subquery that can unnest
> to single row aggregate.
>
> When it's just the statistics and arithmetic that predict
> one row, the table can still move around the join order -
> but the prediction is likely to have a cost implication
> that increased the chance of a Cartesian Join being
> selected. There will be an example of this appearing
> on my blog (and in the Hotsos presentation) in the
> next week or so.
>
> --
> 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

Thanks for the clarification/correction regarding the single-row table case. I look forward to reading your blog entry on this topic.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Feb 25 2007 - 14:22:18 CST