Bitmap scan

During a bitmap scan operation, the entire temporary bitmap is scanned and all the row addresses contained within the bitmap are processed. The optimizer considers this plan when there is an applicable encoded vector index or if the index probe or scan random I/O can be reduced. The random I/O can be reduced by first preprocessing and sorting the row numbers associated with the Table Probe.

The use of a bitmap scan allows the optimizer to generate a plan that can take advantage of multiple indexes to match up to different portions of the query.

An additional benefit is that the data structure of the temporary bitmap guarantees that the row numbers are sorted. It closely mirrors the row number layout of the table data, ensuring that the table paging never visits the same page of data twice. This results in increased I/O savings for the query.

A bitmap scan is identical to a row number list scan operation. The only difference is that the list scan is over a list of row addresses while the bitmap scan is over a bitmap representing the addresses.

Table 1. Bitmap scan attributes
Data access method Bitmap scan attributes
Description Sequentially scan and process all the row numbers in the temporary bitmap. The sorted row numbers can be merged with other temporary bitmaps or can be used as input into a Table Probe operation.
Advantages
  • The temporary bitmap only contains a reference to a row address, no data, so the temporary can be efficiently scanned within memory.
  • The row numbers represented within the temporary object are sorted to provide efficient I/O processing to access 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 sorting of the row numbers is justified by the more efficient I/O that can be performed during the Table Probe operation.
  • 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
OPTIMIZE FOR 99999 ROWS
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 'B'.

A QQRID 3000 record with QQC11 (Skip_Sequential_Table_Scan) = 'Y'.

Optionally, QQRID 3022 records if bitmap merging occurred.
SMP parallel enabled Yes
Also referred to as Bitmap Scan, Preload

Row Number Bitmap Scan

Row Number Bitmap Scan, Preload

Skip Sequential Scan
Visual Explain icon
Bitmap scan icon

Using the example above, the optimizer created a temporary bitmap for each of the indexes used by this query. These indexes included a radix index and two encoded vector indexes. Each index temporary bitmap was scanned and merged into a final composite bitmap representing the intersection of all the index temporary bitmaps. The final bitmap is then used by the Table Probe operation to determine which rows are selected and processed for the query results.