Re: Merge Join (Cartesian)

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:B%ipa.13$vW3.122_at_news.oracle.com...
> "Ty" <housequake_at_yahoo.com> wrote in message
> news:55a9978a.0304220752.6cbfc9a5_at_posting.google.com...
> > Adding the column to the select list causes a cartesian join and the
> > query will run for hours before it finally fails with ORA-01555
> > "Snapshot too old". It seems odd to me that adding a column from the
> > same table to the select list could have such a devasting effect on
> > performance. Have any of you gurus seen anything like this? Oracle
> > support has told me that this is a normal sql tuning issue. Perhaps
> > it is, but I would still like to know what is going on here...
>
> Adding a column to the query changes costing, and it just happens that
> optimizer chooses a cartesian join sucker. I bet estimated cardinality --
> one row for each of the join argument -- is wrong. This problem is so
> common that oracle would better introduce a negative hint -- no cartesian
> join. You can use "ordered" or "leading" hint as a poor "no cartesian
join"
> substitute.
>
>

why does adding a column lead to a cartesian join? using ordered is a real pain when you have a big join. its not always that easy to get the table orders correct? anything you can do? Received on Tue Apr 22 2003 - 17:36:22 CDT