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
>

Are you sure this query returns the results you want it to? Why do you have the Passenger table involved twice, the Flight table involved 3 times, and the Takes table involved twice?

I'm surprised you only have four join operations in your Explain Plan. I see six join conditions in your WHERE clause:

  P1.PassportNum = T1.Passenger
  P2.PassportNum = T2.Passenger
  T1.Flight = F1.FlightCode
  T2.Flight = F2.FlightCode
  P1.PassportNum = P2.PassportNum
  F1.StartCityCode = F2.StartCityCode

Although mapping the joins, I get the following tables being joined:

P1 -> T1
P2 -> T2
T1 -> F1
T2 -> F2
P1 -> P2
F1 -> F2

Which if I combine the first few, I get the following:

P1 -> T1 -> F1
P2 -> T2 -> F2
P1 -> P2
F1 -> F2

Notice P1 joins to P2 which lets me combine the first two sets:

P1 -> T1 -> F1 -> P2 -> T2 -> F2
F1 -> F2

The last join condition is redundant. So that leaves us with:

P1 -> T1 -> F1 -> P2 -> T2 -> F2

The above involves five join operations...unless I missed something along the way.

And you Explain Plan does not match the query as the TAKES table is not involved in any manner in the Explain Plan. Yet the table is present in the query.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown