Re: a question about Explain plan of oracle

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 30 Nov 2006 17:10:09 -0800
Message-ID: <1164935409.354861.158190@16g2000cwy.googlegroups.com>

Brian Peasland wrote:
> 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

>

Under the assumption that the plan was extracted and posted correctly then perhaps because all the data being retrieved from the query is limited to P1 the CBO was able to eliminate accessing some of the tables. I notice that there are a total of 8 table and (system generated named) index accesses.

HTH -- Mark D Powell -- Received on Thu Nov 30 2006 - 19:10:09 CST