Re: Bitmap index costing - how to influence

On Fri, Mar 27, 2009 at 11:18 PM, Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> wrote:
> I appreciate the feedback, but let's not deviate from the subject.
>
> The question is not whether to use bitmap indexes, but how to
> influence the CBO calculations to favor the use of bitmap indexes.

I don't really think I deviated from the subject. To me, part of the subject (the essential part) is understanding why something occurs (or how one got here), which in turn leads to understanding options used to address that problem. That is also part of discussion on a public list/forum. Far too often DBAs get micro focused on what they perceive to be the problem without understanding the bigger picture. Part of this also stems from lack of context and detail provided in your initial request (no version, not EE so no partitioning, etc), but I digress...

> There was no way for you to know this, but the query in question is
> using a range predicate that can vary greatly:
>
> SELECT * from some_table_709MB
> WHERE  julian_date >= 2454892 AND julian_date <= 2454922
>
> So again, the question is how to favor the use of bitmap indexes by
> the CBO without affecting too much b-tree or other access paths.

What is the scope of your issue? Do you have a few, known, queries (from an application, say) or are you trying to address any query that could be written (ad hoc user access)? The potential solutions will depend on this. In the meantime I will make some assertions for both cases.

Known Queries

- hint (INDEX_COMBINE)
- outlines
- sql profiles

Ad Hoc Queries
- optimizer_index_cost_adj (which you mentioned) - optimizer_index_caching

Changing either of the optimizer_index parameters obviously will effect all index costing, so it might be "fixing" more than you would like.

So to specifically answer your question: There is no parameter/way that only adjusts costs of bitmap indexes at a global level.

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l