Re: a question about Explain plan of oracle

yuanhp_china_at_hotmail.com wrote:
> 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

Unless you have an old unsupported version of Oracle this is not an Explain Plan and it doesn't appear to match the SQL you posted.

Please post the correct SQL and the correct Explain Plan created using DBMS_XPLAN. The syntax is: SELECT * FROM TABLE(dbms_xplan.display);

Also, are you using a statement ID to make sure the plan you are grabbing is the one you want?

Perhaps more explicitly you should use:
  SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE', <statement_id>, 'ALL'));

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org