Re: Function Based Index

The fact that the row for the table is much larger than the row estimate for the index used to get to it suggests that you didn't collect stats on the hidden column underpinning the table when you created the index. The plan may have changed the following morning because of an overnight stats collection that rectified this mismatch. It's interesting, though that in this case the cost of the table visits have been added to the cost of the range scan - but perhaps that's a version thing.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Purav Chovatia" <puravc_at_gmail.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> Sent: Thursday, September 13, 2012 8:30 PM Subject: Re: Function Based Index

| In my reply to Stephano, I had mentioned that for once I did see CBO
| choosing the plan with index range scan. I had created the FBI on 7th Sep
| and after creating it, when I tried to verify that indeed uses the
index,I
| had tried explain plan and this is what I had seen (I connect to server
| using putty and putty logging is always enabled and hence could mine out
| from the putty logs):

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