Re: [HELP] unexpected merge cartesian join

nicola.farina_at_info-line.it wrote:
> Hello all
>
> I desperately try to understand why appears a merge cartesian join in a
> query
> changing the select fields.
> The query is

I'd look at the obvious first. Are the statistics on the underlying tables current? Cartesian join is not necessarily a bad thing if one of the tables involved has 0 or 1 rows and in such cases the optimizer correctly chooses that. But if the table actually > 1 row though the stats say 0 or 1, you could be in for a long wait before the query finishes.

This happens a lot in applications that build temporary tables prior to using them in a query. When the analyze ran, hours or days prior, the table was empty and the stats say it has 0 rows. In reality it might have 1000's of rows after it gets rebuilt. In cases like this I suggest deleting the stats from the table altogether, do not ever collect them, and enable dynamic sampling.

Chuck Received on Thu Dec 01 2005 - 13:10:37 CST