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 it matches the selection used to generate the 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 is that the list probe is over a list of row addresses while the bitmap probe is over a bitmap representing the 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 row 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 contains only the addresses of the selected rows in the table, a separate Table Probe fetches 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
Database Monitor and Plan Cache record indicating use

QQRID 3001 and QQRID 3021 records for each index used.

The QQC11 field in the 3021 record will be 'BL'.

Optionally, QQRID 3022 records if bitmap merging occurred.
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 requires that the resulting rows be sequenced by the WorkDept column, the bitmap cannot be scanned for 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 bitmap with the row address extracted from the index probe, the sequencing of the keys in the index X1 is preserved. The row can still be tested against the selected rows within the bitmap.