a question about Explain plan of oracle

From: <yuanhp_china_at_hotmail.com>
Date: 29 Nov 2006 20:43:39 -0800
Message-ID: <1164861819.647516.29710@14g2000cws.googlegroups.com>

I have three tables, Flight, Passengers, Takes:

Flight(FlightCode, Airline, StartCityCode, EndCityCode,dDate) FlightCode is primary ky.

Passenger(PassportNum, Nationality),
PassportNum is key.

Takes(Passenger, Flight)
(assenger, Flight) is key.
Foreign key : Passenger -> Passenger(PassportNum),

              Flight -> Flight(FlightCode).

My sql is :
select P1.* from Passenger P1, Passenger P2, Flight F1, Flight F2, Flight F3, Takes1 T1, Takes1 T2
where

P1.PassportNum = T1.Passenger and
P2.PassportNum = T2.Passenger and
T1.Flight = F1.FlightCode and
T2.Flight = F2.FlightCode and
P1.PassportNum = P2.PassportNum and
P1.Nationality = 'USA' and
F1.StartCityCode = 'Chi' and

 F1.StartCityCode = F2.StartCityCode and F1.dDate = F2.dDate;

I used explain plan to trace it and the result is Execution Plan

---

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=102
          8)

   1    0   NESTED LOOPS (Cost=4 Card=1 Bytes=1028)
   2    1     NESTED LOOPS (Cost=3 Card=1 Bytes=958)
   3    2       NESTED LOOPS (Cost=2 Card=1 Bytes=893)
   4    3         NESTED LOOPS (Cost=2 Card=1 Bytes=823)
   5    4           INDEX (FAST FULL SCAN) OF 'SYS_C00383968' (INDEX (
          UNIQUE)) (Cost=2 Card=1 Bytes=65)

   6    4           TABLE ACCESS (BY INDEX ROWID) OF 'PASSENGER' (TABL
          E) (Cost=0 Card=1 Bytes=758)

   7    6             INDEX (UNIQUE SCAN) OF 'SYS_C00383723' (INDEX (U
          NIQUE)) (Cost=0 Card=1)

   8    3         TABLE ACCESS (BY INDEX ROWID) OF 'FLIGHT' (TABLE) (C
          ost=0 Card=1 Bytes=70)

   9    8           INDEX (UNIQUE SCAN) OF 'SYS_C00383722' (INDEX (UNI
          QUE)) (Cost=0 Card=1)

  10    2       INDEX (RANGE SCAN) OF 'SYS_C00383968' (INDEX (UNIQUE))
           (Cost=1 Card=1 Bytes=65)

  11    1     TABLE ACCESS (BY INDEX ROWID) OF 'FLIGHT' (TABLE) (Cost=
          0 Card=1 Bytes=70)

  12   11       INDEX (UNIQUE SCAN) OF 'SYS_C00383722' (INDEX (UNIQUE)
          ) (Cost=0 Card=1)

Somebody can help me to explain why there are 4 join operation in the plan?

Thanks Received on Wed Nov 29 2006 - 22:43:39 CST