Generally, DBAs ask the equivalent of Oracle’s bitmap indexes in DB2. DB2 optimizer takes away the burden of creating / maintaining bitmap indexes as it creates them on the fly when it needs them.
How do you know if DB2 optimize is using bitmap index or not?
David Sciaraffa explains that you can know about it by looking at the explain plan for the query. One of the example is shown below:
FETCH
/ \
RIDSORT fact table
/
IXAND ----> This is where the "bitmaps" are ANDed
/ | \
NLJN NLJN
/ \ ... / \
D1 IF1 Dn IFn
Note:
IF1 .. IFn are indexes on the fact table that provide
the RIDs used for the bitmap ANDing
D1 ...Dn are dimension tables .... could be accessed
in different ways.
Generally as a DBA, you might have created a bitmap index in Oracle on a low cardinality column but DB2 does this automatically for you.
The problem with static bit map indexes is – you require many of them and they impact the updating of data. An insert, update, delete needs to update all the indexes. DB2 researchers made a decision many years ago to go with dynamic bit map indexes.
There is no disk or update overhead and DB2 can give you similar performance by accessing the index and handling the RIDS in memory. In addition to bitmap indexes, DB2 have hash joins which are lightening fast if you have the memory for them. Basically the same as bit map indexing.