Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    45

    Unanswered: bitmap indexes in DB2

    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??

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2rocks
    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??
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •