Re: Does "Nested Loops" not always join on a join key?

Hello Marcel,

from the execution plan, both DT_INFO and FUND_SHR_SET are read by the full primary key (index unique scan). Though the plan info says 'nested loops', it is logically equal to a cartesian product - of 1 by 1. So they are not joined at all, but cross-joined, in ansi sql-92 terms.

Kind Regards,

Herman de Boer
sr consultant
IT Consultancy Group bv.

Marcel Kraupp wrote:

>Hello
>
>I have a select statement that used five tables. I have
>tried to draw their relationship here:
>
>
> / \
>DT_INFO -<- FUND_SHR_PRPTY ->- FUND_SHR
> \ / |
> |
> |
> \ /|\
> FUND_SHR_SET->--FUND_SHR_LIST
> /
>
>
>(one row of DT_INFO joins to zero, one, or more of FUND_SHR_PRPTY, and so on)
>
>The statement looks like this:
>
>SELECT
> FUND_SHR_LIST.ORDER_SEQ,
> FUND_SHR_PRPTY.NAV_DT,
> FUND_SHR.SEC_NUM,
> FUND_SHR.SHR_NM,
> FUND_SHR_PRPTY.CCY_CD_516
>FROM
> FUND_SHR_LIST,
> FUND_SHR_PRPTY,
> FUND_SHR,
> FUND_SHR_SET,
> DT_INFO
>WHERE
> FUND_SHR_SET.FUND_SHR_SET_ID = FUND_SHR_LIST.FUND_SHR_SET_ID
> AND FUND_SHR_PRPTY.NAV_DT = DT_INFO.LAST_NAV_DT
> AND FUND_SHR_PRPTY.FUND_SHR_ID = FUND_SHR.FUND_SHR_ID
> AND FUND_SHR.FUND_SHR_ID = FUND_SHR_LIST.FUND_SHR_ID
> AND FUND_SHR_SET.FUND_SHR_SET_NM = 'Cyclon (alle WF inkl. Immo)'
> AND DT_INFO.DT = to_date('23.07.2002','dd.mm.yyyy')
>ORDER BY
> 1;
>
>
>And the Execution plan is:
>
>SELECT STATEMENT []
> SORT [ORDER BY]
> NESTED LOOPS []
> NESTED LOOPS []
> NESTED LOOPS []
> NESTED LOOPS []
> TABLE ACCESS [BY INDEX ROWID] DT_INFO <==== Here
> INDEX [UNIQUE SCAN] SYS_C004592
> TABLE ACCESS [BY INDEX ROWID] FUND_SHR_SET <==== and Here
> INDEX [UNIQUE SCAN] IX_FUND_SHR_SET
> TABLE ACCESS [BY INDEX ROWID] FUND_SHR_LIST
> INDEX [RANGE SCAN] SYS_C004617
> TABLE ACCESS [BY INDEX ROWID] FUND_SHR
> INDEX [UNIQUE SCAN] PK_FUND_SHR
> TABLE ACCESS [BY INDEX ROWID] FUND_SHR_PRPTY
> INDEX [RANGE SCAN] IX_FUND_SHR_PRPTY_FUND_SHR_ID
>
>
>Now, I have problems understanding how Oracle gets the data. From how I
>understand, Oracle starts where the execution plan is intented most
>(<==== Here, <=== and Here, respectively). The Nested Loop above indicates,
>that Oracle joins DT_INFO and FUND_SHR_SET. But here's my problem: how
>can Oracle join these table: the don't have a join key in common.
>
>The Version is 8.1.7 on Solaris.
>
>Can anyone please give me any hint?
>
>Thanks
>MK
Received on Tue Aug 06 2002 - 02:16:59 CDT