Encoded vector index symbol table scan

Encoded vector index symbol table scan

An encoded vector index symbol table scan operation is used to retrieve the entries from the symbol table portion of the index.
All entries (symbols) in the symbol table will be sequentially scanned, though the sequence of the resulting entries is not in any guaranteed order. The symbol table can be used by the optimizer to satisfy group by or distinct portions of a query request. Any selection is applied to every entry in the symbol table. All entries are retrieved directly from the symbol table portion of the index without any access to the vector portion of the index nor any access to the records in the associated table over which the EVI is built.
Table 1. Encoded vector index symbol table scan attributes
Data access method Encoded vector index symbol table scan
Description Sequentially scan and process all of the symbol table entries associated with the index. Any selection is applied to every entry in the symbol table. Selected entries are retrieved directly without any access to the vector or the associated table
Advantages

Pre-summarized results are readily available

Only processes the unique values in the symbol table, avoiding processing table records.

Extract all of the data from the index unique key values, thus eliminating the need for a Table Probe or vector scan.

Considerations Dramatic performance improvement for grouping queries where the resulting number of groups is relatively small compared to the number of records in the underlying table. Can perform poorly when there are a large number of groups involved such that the symbol table is very large, especially if a large portion of symbol table has been put into the overflow area.
Likely to be used

When asking for GROUP BY, DISTINCT, COUNT or COUNT DISTINCT from a single table and the referenced column(s) are in the key definition

When the number of unique values in the column(s) of the key definition is small relative to the number of records in the underlying table.

When there is no selection (Where clause) within the query or the selection does not reduce the result set very much.
Example SQL statement
CREATE ENCODED VECTOR INDEX EVI1 ON Sales (Region) 
Example 1
SELECT Region, count(*) 
FROM Sales 
GROUP BY Region OPTIMIZE FOR ALL ROWS
Example 2
SELECT DISTINCT Region FROM Sales 
OPTIMIZE FOR ALL ROWS
Example 3
SELECT COUNT(DISTINCT Region)
FROM Sales 
Messages indicating use
Optimizer Debug:
CPI4328 -- Access path of file EVI1 was used by query. 
PRTSQLINF:
SQL4008 -- Index EVI1 used for table 1.SQL4010
SMP parallel enabled No. Typically not critical as the 'grouping' has already been performed during the index build.
Also referred to as Encoded Vector Index table scan, Preload
Visual Explain icon
Parent topic:
Encoded vector index

Data access method	Encoded vector index symbol table scan
Description	Sequentially scan and process all of the symbol table entries associated with the index. Any selection is applied to every entry in the symbol table. Selected entries are retrieved directly without any access to the vector or the associated table
Advantages	Pre-summarized results are readily available Only processes the unique values in the symbol table, avoiding processing table records. Extract all of the data from the index unique key values, thus eliminating the need for a Table Probe or vector scan.
Considerations	Dramatic performance improvement for grouping queries where the resulting number of groups is relatively small compared to the number of records in the underlying table. Can perform poorly when there are a large number of groups involved such that the symbol table is very large, especially if a large portion of symbol table has been put into the overflow area.
Likely to be used	When asking for GROUP BY, DISTINCT, COUNT or COUNT DISTINCT from a single table and the referenced column(s) are in the key definition When the number of unique values in the column(s) of the key definition is small relative to the number of records in the underlying table. When there is no selection (Where clause) within the query or the selection does not reduce the result set very much.
Example SQL statement	CREATE ENCODED VECTOR INDEX EVI1 ON Sales (Region) Example 1 SELECT Region, count(*) FROM Sales GROUP BY Region OPTIMIZE FOR ALL ROWS Example 2 SELECT DISTINCT Region FROM Sales OPTIMIZE FOR ALL ROWS Example 3 SELECT COUNT(DISTINCT Region) FROM Sales
Messages indicating use	Optimizer Debug: CPI4328 -- Access path of file EVI1 was used by query. PRTSQLINF: SQL4008 -- Index EVI1 used for table 1.SQL4010
SMP parallel enabled	No. Typically not critical as the 'grouping' has already been performed during the index build.
Also referred to as	Encoded Vector Index table scan, Preload
Visual Explain icon