Re: need to understand merge join cartesian plan

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Sun, 17 Jan 2010 16:22:29 +0100
Message-ID: <4B532B35.7040909_at_roughsea.com>

joshuasingham wrote:
> Hi all,
>
>
> 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
>
> | 4 | MERGE JOIN CARTESIAN |
> | 1 | 63 | 252K (1)| 00:42:57 |
> | |
> | 5 | TABLE ACCESS FULL | GL_CODE_COMBINATIONS |
> 36080 | 1761K| 176 (2) | 00:00:02 | | |
> | 6 | BUFFER SORT |
> | 1 | 13 | 18E(100)|999:59:59
> | | |
> | 7 | VIEW | VW_NSO_1
> | 1 | 13 | 7 (0)| 00:00:01 |
> | |
>
>
>
>
> thanks
>
> Joshua
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

Joshua,

   It's very hard to decide whether the optimizer is doing something wrong or not without seeing the query, having some idea about the size of the tables and the various indexes involved - at which point I usually find the execution plan pretty useless. Looks like transcoding, with an inline view. One possibility (but I'm just quessing) is that you have something in your inline view that prevents the optimizer to rewrite the query, perhaps some aggregate, and that you are "losing" your indexes (assuming they are any useful) in the process.

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jan 17 2010 - 09:22:29 CST