Re: Bitmap index costing - how to influence

If you have any suggestions for 11g, they are welcome, an upgrade is possible, should this be resolved there.

On Fri, Mar 27, 2009 at 4:47 PM, Bobak, Mark <Mark.Bobak_at_proquest.com> wrote:
> Christo,
>
> What version of Oracle??
>
> -Mark
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christo Kutrovsky
> Sent: Friday, March 27, 2009 4:41 PM
> To: oracle-l
> Subject: Bitmap index costing - how to influence
>
> Hello List,
>
> 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?
>
> --
> Christo Kutrovsky
> Senior DBA
> The Pythian Group - www.pythian.com
> I blog at http://www.pythian.com/blogs/
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>

-- 
Christo Kutrovsky
Senior DBA
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
http://www.freelists.org/webpage/oracle-l