Hello all,

Could some one here tell me about bitmap indexes in db2.I know that we can create bitmap indexes manually in Oracle.I read somewhere that Db2 dynamically builds bitmap indexes.Suppose there is column called gender with two values 'M' and 'F' and there are 12 million rows in the table.Does it consider bitmap index when this column is used in the joins in the queries??

DB2 LUW uses bitmaps during index-anding: http://publib.boulder.ibm.com/infoce.../c0005301.html

p.s: Using bitmap indexes on a gender column is not very helpful. This column has a low selectivity, i.e. about half of the rows will qualify.

Since there are only 2 values in the GENDER column, DB2 will assume that about 50% of the rows in page are 'M' and 50% are 'F'. The only exception would be if the distribution was highly skewed (such as a list of American high school football players) and you ran the appropriate runstats to gather the actual distribution.

Given the above, DB2 will have to access each data page in the table. DB2 will typically only choose to use an index if such index can be used to avoid reading at least some data pages. Doesn't matter what kind of index it is, unless the column happens to be the clustering index (the data rows are ordered by GENDER).