Re: Function Based Index

From: Stefano Cislaghi <s.cislaghi_at_gmail.com>
Date: Mon, 10 Sep 2012 17:58:29 +0200
Message-ID: <CAFsgGrySFtFonm_3SH=+XYboTpctpe8ojdR5Vz336nWsSAs=6Q_at_mail.gmail.com>

I agree. Anyway if you do not provide us more information and the query anything we might say are only theory and guessworks.

Ste

On 10 September 2012 17:48, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
> Maybe the CBO is choosing not to use the index because the percentage of blocks it thinks you will need is too high? I don't know exactly how the CBO calculates it, but the general rule of thumb is to only use an index if you're going to read less than 5% of the table's blocks. If the values in your look up column are skewed, you could try running the query with an unpopular value such that Oracle will be more likely to use an index (make sure you flush the old plans out of the pool first), and then, if it does load the plan with the index, you can create a sql plan baseline to make it stick with that plan for future executions - that is assuming you're on 11g; create a stored outline if you're on 10g.
>
> Regards,
> Brandon
>
>
>
> ________________________________
>
> Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 
http://www.stefanocislaghi.eu

The SQLServerAgent service depends on the MSSQLServer service, which
has failed due to the following error: The operation completed
successfully.
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 10 2012 - 10:58:29 CDT