Tips for dynamic bitmap

As the name implies, this method generates bitmaps that are used during access to the table data. The bitmap processing method is used to:

In this method, the optimizer chooses one or more indexes to be used in selecting the rows. Instead of using the indexes to retrieve the data from the table data (using key positioning and/or key selection access method), the indexes are used to create a temporary bitmap. One bitmap is created for each index used. Each bitmap contains one bit for each row in the underlying table data. The bit is turned on for each row that is to be selected. If multiple bitmaps are created, they are merged or combined to form a final bitmap. The final bitmap is then used to determine which rows will be retrieved from the table data. A random I/O is performed to retrieve the selected rows. The merging of the bitmaps simulates the boolean logic (AND/OR logic) in the query's selection.

The bitmap processing method is used in conjunction with primary access methods table scan, key selection, or key positioning. Bitmap processing, like parallel pre-fetch and parallel table/index pre-load, does not actually select the records from the table data; it assists the primary methods.

If the bitmap is used in conjunction with the table scan method, the bitmap initiates a skip sequential processing. The table scan (and parallel table scan) uses the bitmap to "skip over" non-selected records. This has several advantages:

The following example illustrates a query where the query optimizer chooses the bitmap processing method in conjunction with the table data scan:

CREATE INDEX IX1 ON EMPLOYEE (WORKDEPT)
CREATE INDEX IX2 ON EMPLOYEE (SALARY)

 DECLARE C1 CURSOR FOR
  SELECT * FROM EMPLOYEE
   WHERE WORKDEPT = 'E01' OR SALARY>50000
   OPTIMIZE FOR 99999 ROWS

In the previous example, a bitmap is created from index IX1 (WORKDEPT='E01') and another bitmap is created from index IX2 (SALARY>5000). These bitmaps are merged together to form a final btimap, which is used to select the rows from the table data.

For more information, see Database Performance and Query Optimization in the Information Center Link to Information center.