Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> a question about Explain plan of oracle
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
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