Re: Bitmap index costing - how to influence
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: Martin Berger <martin.a.berger@xxxxxxxxx>
- Date: Fri, 27 Mar 2009 18:12:57 -0400
There are no statistics that I can manipulate on a bitmap index that
will affect the cost by much. In this case the index access cost is 30
out of the entire cost of the query of 180 000.
Bitmap indexes use mainly assumptions in regards to table access cost.
On Fri, Mar 27, 2009 at 5:33 PM, Martin Berger
<martin.a.berger@xxxxxxxxx> wrote:
>> I am pretty much stuck on a bitmap costing problem.
>>
>> I have a query on a table with 10M rows (709MB), and the predicates
>> are correctly estimated to return 800 000 rows. As a result with the
>> famous 80/20 split, my bitmap index access path cost is ~170 000. The
>> full table scan cost is ~20 000. Obviously Oracle choose FTS.
>>
>> When executed with a hint, the query touches about 8000 block from the
>> table, and needless to say, is significantly faster.
>>
>> Any ideas on how to influence bitmap index cost, relative to full
>> table scan cost? Anything goes as long as it doesn't break b-tree
>> index costing.
>>
>> Thing's I've already considered, but somewhat ruled out
>> - adjust "mbrc" system statistic - event at max (128) cost is still
>> 133 000 vs 16 000 fts
>> - optimizer_index_cost_adj - i will have to set this to 10 (10 times
>> cheaper) for bitmaps to be used. I am concerned about proper
>> calculations of b-tree indexes vs full table scans
>>
>> Any other ideas?
>
> Just an idea: manipulate the statistics of your index manually.
> This should led the CBO to other calculations and decisions.
>
> Maybe not the finest way, but at least a method to manipulate only one
> object rather than changing instance parameters.
>
> hth
> Martin
>
>
--
Christo Kutrovsky
Senior DBA
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
