RE: Function Based Index
Date: Tue, 18 Sep 2012 06:48:43 -0500
Message-ID: <8C0087E93F6A9A459051869F5D574891EAD262_at_LISL-XBCK-C1-V2.snaponglobal.com>
By the way, did you notice that your statement: delete from <table_name> where
trunc(datetime) < (trunc(sysdate) - 31) and rownum < 1000;
can also be written as:
delete from <table_name> where
datetime < (trunc(sysdate) - 31) and rownum < 1000;
So, if this is the only reason you have that FBI, you might as well fix the statement and replace the FBI with an index directly on datetime and save yourself some mysteries.
Mike Tefft
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Purav Chovatia
Sent: Monday, September 17, 2012 10:35 AM
To: Jonathan Lewis
Cc: oracle-l_at_freelists.org
Subject: Re: Function Based Index
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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 18 2012 - 06:48:43 CDT