Re: SQL query clarification

Hey Ram,
unfortunately such kind of inconsistencies are not very uncommon. You did not mention your database version (and the outline section is missing), but it definitely looks like bug #12727549. However the impact in your case is zero as join cardinality is correct :-)

Wanna see some other example of such "inconsistencies"? Check Jonathan's slide on page 7 here: http://www.doag.org/formes/servlet/DocNavi?action=getFile&did=6436049&key=

You can also execute your statement with the gather_plan_statistics hint and you gonna see the "real returned rows".

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

> Ram Raman <veeeraman_at_gmail.com> hat am 5. Januar 2017 um 05:25 geschrieben:
>
> List
>
> I was trying to tune a query in 11g EE. Here is the link: https://drive.google.com/open?id=0B9YC82qZ8_3eemF5b3kzTlh0eVU
>
> I was looking at the plan and one thing i could not understand was ids 8 and 9 in the second plan (phv 1362929457). After getting the employeeid
> from the TIMESHEEITEM table, the optimizer selects the matching row from the jaids table. jaids table has PERSONID as primary key. Why would the
> optimizer say that it would (or really did) access all the 31.5K rows of JAIDS. Same question with PERSON table.
>
> Ram.
>
> --
 

--
http://www.freelists.org/webpage/oracle-l