Encoded vector index probe

Encoded vector index probe

The encoded vector index (EVI) is quickly probed based upon the selection criteria that were rewritten into a series of ranges. It produces either a temporary row number list or bitmap.
Table 1. Encoded vector index probe attributes
Data access method Encoded vector index probe
Description The encoded vector index (EVI) is quickly probed based upon the selection criteria that were rewritten into a series of ranges. It produces either a temporary row number list or bitmap.
Advantages

Only those index entries that match any selection continue to be processed

Provides very quick access to the selected rows

Returns the row numbers in ascending sequence so that the Table Probe can be more aggressive in pre-fetching the rows for its operation

Considerations EVIs are generally built over a single key. The more distinct the column is and the higher the overflow percentage, the less advantageous the encoded vector index becomes. EVIs always require a Table Probe to be performed on the result of the EVI probe operation.
Likely to be used

When the selection columns match the leading key columns of the index

When an encoded vector index exists and savings in reduced I/O against the table justifies the extra cost of probing the EVI and fully populating the temporary row number list.
Example SQL statement
CREATE ENCODED VECTOR INDEX EVI1 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
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 EVI1 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 Encoded Vector Index Probe, Preload
Visual Explain icon
Using the example above, the optimizer chooses to create a temporary row number bitmap for each of the encoded vector indexes used by this query. Each bitmap only identifies those rows that match the selection on the key columns for that index. These temporary row number bitmaps are then merged together to determine the intersection of the rows selected from each index. This intersection is used to form a final temporary row number bitmap that will be used to help schedule the I/O paging against the table for the selected rows.
The optimizer might choose to perform an index probe with a binary radix tree index if an index existed over all three columns. The implementation choice is probably decided by the number of rows to be returned and the anticipated cost of the I/O associated with each plan. If very few rows will be returned, the optimizer probably choose to use the binary radix tree index and perform the random I/O against the table. However, selecting more than a few rows will cause the optimizer to use the encoded vector indexes because of the savings associated with the more efficient scheduled I/O against the table.

Parent topic:
Encoded vector index

Data access method	Encoded vector index probe
Description	The encoded vector index (EVI) is quickly probed based upon the selection criteria that were rewritten into a series of ranges. It produces either a temporary row number list or bitmap.
Advantages	Only those index entries that match any selection continue to be processed Provides very quick access to the selected rows Returns the row numbers in ascending sequence so that the Table Probe can be more aggressive in pre-fetching the rows for its operation
Considerations	EVIs are generally built over a single key. The more distinct the column is and the higher the overflow percentage, the less advantageous the encoded vector index becomes. EVIs always require a Table Probe to be performed on the result of the EVI probe operation.
Likely to be used	When the selection columns match the leading key columns of the index When an encoded vector index exists and savings in reduced I/O against the table justifies the extra cost of probing the EVI and fully populating the temporary row number list.
Example SQL statement	CREATE ENCODED VECTOR INDEX EVI1 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	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 EVI1 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	Encoded Vector Index Probe, Preload
Visual Explain icon