Hacking the plan table (30th Dec 2005)
Index Decompression (14th Dec 2005)
Page 186: There are a couple of extracts from the 10053 trace showing that Oracle calculates costs for bitmap indexes to (at least) two decimal places. And later in the chapter I have taken advantage of the extra precision that is visible in the trace file to get some answers to how the cost calculations work. It is possible to make this precision visible in the execution plans themselves instead of resorting to 10053 traces though. The topic is covered in a note on my main website.
Page 191: Paragraph 2. The closing sentence of the paragraph makes a comment that by choosing the order of indexes carefully, the execution engine may “reduce the number of bitmap index fragments it has to expand and compare”.
I have had a note from Ghassan Salem pointing out that the code does not have to expand (or uncompress) bitmaps to do the “bitmap or” and “bitmap and”; the comparison can be made whilst the bitmaps are still compressed. In part this is due to the type of compression used. He also gave me the URL to a paper presented at the VLDB conference in 1999 discussing different methods of compression, and their costs and benefits. The paper can be found at http://www.vldb.org/conf/1999/P29.pdf and it makes very interesting reading. If you do download it, Oracle’s choice of compression is the one called BBC 1-sided. – Oracle stores the one’s but uses the zeros as end-markers and compresses them.
Remember, though, that Oracle still decompresses indexes for the “bitmap merge” operation; and it was for the benefit of this operation that the parameter bitmap_merge_area_size was created (now just one of the workarea operations if you are using pga_aggregate_target) and if you expect to do a lot of “bitmap merge” you should also be thinking about using the table-related option “minimize_records_per_block” to get the maximum index compression and best use of the available merge memory.
Note – “bitmap merge” is the bitmap operation that combines several sections of a bitmap index after a range scan or in-list operator. In some ways it is surprising that it needs to expand the bitmaps, since it doesn’t sound as if this operation should be anything more than a series of “bitmap or” operations on the same index – but “bitmap or” on BBC1 compressed bitmap indexes can become inefficient, so possibly this is to pre-empt that effect.