Bitmap scan

Bitmap scan

During a bitmap scan operation, the entire temporary bitmap is scanned and all of the row addresses contained within the bitmap will be processed. A bitmap scan is generally considered when the optimizer is considering a plan that involves an encoded vector index or if the cost of the random I/O associated with an index probe or scan operation can be reduced by first preprocessing and sorting the row numbers associated with the Table Probe operation.
The use of a bitmap scan will allow 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 of using a bitmap scan 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 paging on the table will never revisit 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 between the two operations is that a row number list scan is performed over a list of row addresses while the bitmap scan is performed over a bitmap that represents the row addresses.
Table 1. Bitmap scan attributes
Data access method Bitmap scan attributes
Description Sequentially scan and process all of 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 rows' 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 only contains the addresses of the selected row 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 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
Messages indicating use
There are multiple ways in which a bitmap scan can be indicated through the messages. The messages in this example illustrate how the Classic Query Engine will indicate a bitmap scan was used.
Optimizer Debug:
CPI4329 -- Arrival sequence was used for file            EMPLOYEE.
CPI4338 -– 3 Access path(s) used for bitmap            processing of file EMPLOYEE.
PRTSQLINF:
SQL4010 -- Table scan access for table 1.
SQL4032 -- Index X1 used for bitmap            processing of 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 Scan, Preload
Row Number Bitmap Scan
Row Number Bitmap Scan, Preload
Skip Sequential Scan
Visual Explain icon
Using the example above, the optimizer created a temporary bitmap for each of the indexes used by his query. This query used a combination of a radix index and two encoded vector indexes to create the row number lists. The temporary bitmaps for each index were scanned and merged into a final composite bitmap that represents the intersection of the rows represented by all of the temporary bitmaps. The final bitmap is then used by the Table Probe operation to determine what rows are selected and need to be processed for the query results.

Parent topic:
Temporary bitmap

Data access method	Bitmap scan attributes
Description	Sequentially scan and process all of 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 rows' 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 only contains the addresses of the selected row 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 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
Messages indicating use	There are multiple ways in which a bitmap scan can be indicated through the messages. The messages in this example illustrate how the Classic Query Engine will indicate a bitmap scan was used. Optimizer Debug: CPI4329 -- Arrival sequence was used for file EMPLOYEE. CPI4338 -– 3 Access path(s) used for bitmap processing of file EMPLOYEE. PRTSQLINF: SQL4010 -- Table scan access for table 1. SQL4032 -- Index X1 used for bitmap processing of 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 Scan, Preload Row Number Bitmap Scan Row Number Bitmap Scan, Preload Skip Sequential Scan
Visual Explain icon