RE: Function Based Index
From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Mon, 10 Sep 2012 15:48:50 +0000
Message-ID: <A250F0C68C23514CA9F3DF63D60EE10E1A833F64_at_onews32>
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.
Date: Mon, 10 Sep 2012 15:48:50 +0000
Message-ID: <A250F0C68C23514CA9F3DF63D60EE10E1A833F64_at_onews32>
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-lReceived on Mon Sep 10 2012 - 10:48:50 CDT