Bitmap probe

 

A bitmap probe operation is used to test row numbers generated by a separate operation against the selected rows of a temporary bitmap. The row numbers can be generated by any operation that constructs a row number for a table. That row number is then used to probe into a temporary bitmap to determine if that row number matches the selection used to generate the temporary bitmap.

The use of a bitmap probe operation allows the optimizer to generate a plan that can take advantage of any sequencing provided by an index, but still use the bitmap to perform additional selection before any Table Probe operations.

A bitmap probe is identical to a row number list probe operation. The only difference between the two operations is that a row number list probe is performed over a list of row addresses while the bitmap probe is performed over a bitmap that represents the row addresses.

Table 1. Bitmap probe attributes
Data access method Bitmap probe attributes
Description The temporary bitmap is quickly probed based upon the row number generated by a separate operation.
Advantages

  • The temporary bitmap only contains a reference to a rows' address, no data, so the temporary can be efficiently probed within memory

  • The row numbers represented within the bitmap are sorted to provide efficient lookup processing to test the underlying table

  • Selection is performed as the bitmap is generated to subset the number of selected rows in the temporary object
Considerations Since the bitmap only contains the addresses of the selected rows in the table, a separate Table Probe operation must be performed in order to fetch the table rows
Likely to be used

  • When the use of temporary results is allowed by the query environmental parameter (ALWCPYDTA)

  • When the cost of creating and probing the bitmap is justified by reducing the number of Table Probe operations that must be performed

  • When multiple indexes over the same table need to be combined in order to minimize the number of selected rows
Example SQL statement 
CREATE INDEX X1 ON Employee (WorkDept)
CREATE ENCODED VECTOR INDEX EVI2 ON      Employee (Salary)
CREATE ENCODED VECTOR INDEX EVI3 ON      Employee (Job)

SELECT * FROM Employee WHERE WorkDept = 'E01' AND Job = 'CLERK'
AND Salary = 5000
ORDER BY WorkDept
Messages indicating use There are multiple ways in which a bitmap probe can be indicated through the messages. The messages in this example illustrate how the Classic Query Engine will indicate a bitmap probe was used.

  • Optimizer Debug: 
    CPI4328 -- Access path of file X1 was used by query.
CPI4338 -– 2 Access path(s) used for bitmap            processing of file EMPLOYEE.

  • PRTSQLINF: 
    SQL4008 -- Index X1 used for table 1.
SQL4011 -- Index scan-key row positioning            used on table 1.
SQL4032 -- Index EVI2 used for bitmap            processing of table 1.  
SQL4032 -- Index EVI3 used for bitmap            processing of table 1.
SMP parallel enabled Yes
Also referred to as Bitmap Probe, Preload

Row Number Bitmap Probe

Row Number Bitmap Probe, Preload

Visual Explain icon

Bitmap probe icon

Using the example above, the optimizer created a temporary bitmap for each of the encoded vector indexes. Additionally, an index probe operation was performed against the radix index X1 to satisfy the ordering requirement. Since the ORDER BY clause requires that the resulting rows be sequenced by the WorkDept column, the temporary bitmap can no longer be scanned to process the selected rows. However, the temporary bitmap can be probed using a row address extracted from the index X1 used to satisfy the ordering. By probing the temporary bitmap with the row address extracted from index probe operation, the sequencing of the keys in the index X1 are preserved and the row can still be tested against the selected rows within the bitmap.

 

Parent topic:

Temporary bitmap