"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.
Received on Tue Apr 22 2003 - 16:57:47 CDT
