Re: Bitmap index costing - how to influence

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Sat, 28 Mar 2009 02:18:04 -0400
Message-ID: <52a152eb0903272318g4c26625fs389400cac4e3229d_at_mail.gmail.com>



Hi Greg,

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.

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.

On Sat, Mar 28, 2009 at 1:16 AM, Greg Rahn <greg_at_structureddata.org> wrote:
> 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:
> http://structureddata.org/2008/05/29/using-bitmap-indexes-effectively/
>
> On Fri, Mar 27, 2009 at 1:40 PM, Christo Kutrovsky
> <kutrovsky.oracle_at_gmail.com> 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.
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>

-- 
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
Received on Sat Mar 28 2009 - 01:18:04 CDT

Original text of this message