Re: bitmap index not used

Hi Kenny,

Simple example to illustrate a point.

Assume your very large table stores 10 rows per block (and there a heaps of the buggers).

Assume your "very non-distinctive" index has 2 distinct values.

When using the so-called inefficient FTS, Oracle reads every block. But it does so by ensuring it only reads each block once, it does so by reading multiple blocks at a go with the one I/O operation (maybe) and it does so in parallel (maybe). Worst case scenario, the total time equates to the time it takes to perform the number of blocks number of I/Os (plus any other processing it might do such as sorts). The best case scenario substantially better than that.

When using the so-called preferred bitmap index, Oracle needs to read the bitmap index which although stored in an efficient manner, is still an overhead (and potentially quite a number of blocks). However it then needs to read 50% of all rows in the table (as that's the cardinality in this simple example). Therefore it needs to read on average each and every block *5* times as on average we require 5 of the 10 rows stored in each block. Note in this case each block read represents one logical I/O as we have no multiblock read capability here and also no parallel capability. If the index clustering factor is nice and high (meaning many of the same values live in the same block), this might mean in a best case scenario that we could read half the number of blocks *10* times each and not touch the others. This doesn't reduce the logical I/Os but it might help to reduce the physical I/Os as the required blocks have a better chance of being cached (but then again if it's a really big table, maybe not).

Therefore although the bitmap index is relatively efficient to read (compared to it's low cardinality B tree equivalent) it still can be grossly inefficient to use it to access a high proportion of the rows. In this example, the FTS is substantially more efficient (in the order of 100 - 200 times more efficient).

And the CBO calculates this out as well. It basically says, "the bitmap index, oh I don't think so ...".

Now if you had several bitmap indexes using and conditions to reduce the number of accessed rows, then the CBO might show some interest.

Assuming your query requires a large proportion of the rows to be accessed, the best way to tune the query it is to tune the factors I mentioned above. Ensure the db_file_multiblock_read_count is set appropriately. Use parallel process effectively which means ensuring your data is spread effectively across multiple devices. If sorting is involved, ensure the sort_area_size and temporary tablespaces are tune appropriately. And so on.

And of course ensure the statistics are accurate in the first place.

It's quite likely that the CBO is actually doing the best it can.

Cheers

Richard
"Kenny Yu" <kyu_at_biodiscovery.com> wrote in message news:uppmbh3oeo7436_at_corp.supernews.com...
> A column in a large table has very non-distinctive values. I created a
> bitmap index on it. When I query
> select * from mytable where bmp_indexed_column = 'aValue'
> the explain plan shows a table scan was used, even if I added hint /*
index
> (mybmpindexname)*/.
> Running O9i on linux.
> how do i tuning such queries?
>
> Kenny
>
>
Received on Thu Oct 03 2002 - 20:10:31 CDT