Re: Merge Join (Cartesian)

From: Ty <housequake_at_yahoo.com>
Date: Wed, 23 Apr 2003 00:59:38 GMT
Message-ID: <_zlpa.251703$S14.81041@news1.central.cox.net>

Mikito Harakiri wrote:
> "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.
>
>

So, what Oracle support told me is true, that adding a column does indeed effect optimizer processing. Fair enough. But it also seems fair to say the optimizer is handling this query very poorly. I can at least get the query to finish using hints, but it takes close to an hour.

Thanks,
Ty Received on Tue Apr 22 2003 - 19:59:38 CDT