Re: Bitmap index costing - how to influence

From: Greg Rahn <>
Date: Fri, 27 Mar 2009 22:16:40 -0700
Message-ID: <>

In this case I would suggest looking to see if partitioning can be used. I think it may be a better fit. Here is why: On an absolute scale, 800,000 rows returned could mean 800K single block reads which is 800K I/Os (granted, some rows may be in the same block). Compare that to 800 1MB multi-block reads, which is more than the size of the table (709MB). This is why the costing model favors the FTS by quite a bit - because the entire table can be read with fewer number of I/Os.

What if you try this:
Make a copy of the 10M row table that contains only the 800K rows you want returned and compare doing a FTS on 800K to getting the 800K out of 10M using the bitmap index. Clear your buffer cache before each. Make sure your multi-block I/O is 1MB. I suspect the FTS will be faster as it will have, at most, 8000 blocks and assuming a MBRC of 128 and block size of 8k, 8000/128=62.5 so now you are talking about reading, at most, 63 or so 1MB I/Os vs the number of single block I/Os it takes to fetch the 8000 blocks via the bitmap index, 1 block at a time.

Understandably, if you have a perfect scenario, the bitmap index plan could be faster. Those conditions being that the index and table blocks are in memory. On the extreme end, if the table is sorted by the bitmap indexed column, then you would have extreme row locality, meaning the first row in the block would could cause a physical I/O, but each subsequent row in the block would have a logical I/O.

This might be a useful resource:

On Fri, Mar 27, 2009 at 1:40 PM, Christo Kutrovsky <> wrote:
> 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.

Greg Rahn
Received on Sat Mar 28 2009 - 00:16:40 CDT

Original text of this message