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

"Charles Hooper" <hooperc2000_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.
>
> 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.
>

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