Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: [HELP] unexpected merge cartesian join

Re: [HELP] unexpected merge cartesian join

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Thu, 01 Dec 2005 19:10:37 +0000
Message-ID: <1133464237.71fa85b16d044fcd48c3ace96e570f91@news.nntpserver.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US