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

From: Marcel Kraupp <Marcel.Kraupp_at_gmx.ch>
Date: 5 Aug 2002 10:54:33 -0700
Message-ID: <6e901404.0208050954.8dd03fb@posting.google.com>

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 Mon Aug 05 2002 - 12:54:33 CDT