Encoded vector index RRN probe

A table probe operation is used to retrieve a specific row from a table based upon its row number. The row number is provided to the table probe access method by some other operation that generates a row number for the table.

The encoded vector index (EVI) RRN probe is an index only access method that is used to provide selected columns by retrieving the value from the EVI instead of using a table probe to access the table. Retrieving the value from the EVI should provide better I/O characteristics than the random I/Os associated with a table probe operation.

This access method is used in conjunction with a radix index probe, radix index scan, or EVI probe operation. The radix index probe, radix index scan, or EVI probe operation is used to select the rows and then the RRN of the selected row is used to probe into EVIs to retrieve any selected values that were not provided by the index used for selection. The EVI RRN probe can access multiple EVIs to provide selected values.

Table 1. EVI RRN probe attributes
Data access method EVI RRN Probe
Description The encoded vector index (EVI) is quickly probed based upon the RRNs provided by the underlying index access.
Advantages
  • Potential to extract all the data from the EVI index key values, thus eliminating the need for a Table Probe
  • Provides better paging characteristics than a Table Probe.
Considerations
  • Only single key EVIs are considered for this implementation
  • All selected columns must have a single column EVI created.
  • The EVIs must fit in the query’s fair share of optimizer memory
Likely to be used
  • When the table row size is wide, the number of select columns is small compared to the number of columns in the table and the query requires a table probe to retrieve columns
Example SQL statement 
CREATE ENCODED VECTOR INDEX EVI1 ON
Employee (WorkDept)
CREATE ENCODED VECTOR INDEX EVI2 ON
Employee (Salary)WITH 10000 DISTINCT VALUES
CREATE ENCODED VECTOR INDEX EVI3 ON
Employee (LASTNAME)WITH 100000 DISTINCT VALUES
CREATE INDEX IX1 ON Employee (Job)
SELECT LASTNAME, WORKDEPT, SALARY
FROM EMPLOYEE
WHERE JOB = ‘ANALYST’
Database Monitor and Plan Cache record indicating use A QQRID 3001 Index Used record for each EVI with QQRCOD = ‘I8’
SMP parallel enabled Yes
Also referred to as Table Probe, Preload
Visual Explain icon
Table probe icon

Prior to encoded vector index only access (EOA), the recommendation had been to only create EVIs for column with low cardinality (small number of distinct values). This recommendation has now changed. EVI RRN Probe can be used for columns with high cardinality (large number of distinct values). However, when creating the EVI, the WITH integer DISTINCT VALUES clause should be used to set the initial size of the codes appropriately and to minimize maintenance time if the database manager needs to use a larger code. See the CREATE INDEX statement in the SQL Reference for more details.