Re: need to understand merge join cartesian plan
From: Greg Rahn <greg_at_structureddata.org>
Date: Sun, 17 Jan 2010 10:02:31 -0800
Message-ID: <a9c093441001171002v2f99c3a3ia72acd9edd3234bb_at_mail.gmail.com>
I know of a bug (8292914) which fits a similar description (not sure if it is publicly viewable). I filed it against 11.1.0.7 where the MJC cardinality is calculated wrong - its only the cardinality of one side of the join, not the left * right. I'm not sure it if is present in previous releases.
Date: Sun, 17 Jan 2010 10:02:31 -0800
Message-ID: <a9c093441001171002v2f99c3a3ia72acd9edd3234bb_at_mail.gmail.com>
I know of a bug (8292914) which fits a similar description (not sure if it is publicly viewable). I filed it against 11.1.0.7 where the MJC cardinality is calculated wrong - its only the cardinality of one side of the join, not the left * right. I'm not sure it if is present in previous releases.
The workaround is to set _optimizer_mjc_enabled=false to disable the MJC.
On Sun, Jan 17, 2010 at 7:14 AM, joshuasingham <joshuasingham_at_gmail.com> wrote:
> I have a excerpt for a execution plan that look like below which i
> got from dbms.xplan display cursor and would like to know why when the
> merge join cartesian happens Oracle estimate that the will be only 1
> row return should it not be 36080 * 1 = 36080 as this is a Cartesian
> join the Db version is 10.2.0.3
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 17 2010 - 12:02:31 CST