RE: Bitmap index costing - how to influence

Christo,

What version of Oracle??

-Mark

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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




--
http://www.freelists.org/webpage/oracle-l


Other related posts: