Re: Function based nvl index
From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 23 Apr 2013 13:31:05 +0100
Message-ID: <CABe10sbU8vDTi8hzeJq28nyvFz01hEhrhjh2tw117uvUT6wq2g_at_mail.gmail.com>
How many nulls do you have as a percentage of the table, and does Oracle make a good estimate for this figure? Do you have any histograms in the column, as I expect you have 2 popular values and very little else? Formatted output from dbms_xlpan.display_cursor with plan execution stats would be extremely useful
On Apr 23, 2013 1:17 PM, <rajugaru.vij_at_gmail.com> wrote:
Date: Tue, 23 Apr 2013 13:31:05 +0100
Message-ID: <CABe10sbU8vDTi8hzeJq28nyvFz01hEhrhjh2tw117uvUT6wq2g_at_mail.gmail.com>
How many nulls do you have as a percentage of the table, and does Oracle make a good estimate for this figure? Do you have any histograms in the column, as I expect you have 2 popular values and very little else? Formatted output from dbms_xlpan.display_cursor with plan execution stats would be extremely useful
On Apr 23, 2013 1:17 PM, <rajugaru.vij_at_gmail.com> wrote:
> Hi,
> I have a situation where my function based index is not getting used. Its
> an NVL based functional index
>
> NVL(gender,0) =0
>
> Is my where clause. And I have a index on NVL(gender,0)
>
> It was getting used previously, but not now.
>
> I use the some in update and select queries both the cases, index is not
> getting used.
>
> Using 10g R2
>
> Any thoughts?
> Sent on my BlackBerry® from Vodafone--
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 23 2013 - 14:31:05 CEST