Re: Function Based Index

From: Purav Chovatia <puravc_at_gmail.com>
Date: Mon, 17 Sep 2012 20:05:00 +0530
Message-ID: <CADrzpjGZgtDgm2vRfVeRVU18FfXHqHLjnF7=3cmBwEM-2uDczw_at_mail.gmail.com>

That is exactly what I thought. But I remember having created the index with the 'compute statistics' clause. And in 10g, because statistics are automatically gathered for the hidden column too, I believe that statistics would have been correctly present for the hidden column. But I will test and revalidate this understanding.
Thanks.

On Fri, Sep 14, 2012 at 6:54 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

>
> 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

Received on Mon Sep 17 2012 - 09:35:00 CDT