Re: need to understand merge join cartesian plan
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: joshuasingham@xxxxxxxxx
- Date: Sun, 17 Jan 2010 16:22:29 +0100
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
> --
> //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>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
