Tips for encoded vector selection

Some advantages of an encoded vector index include:

Generally, an encoded vector index is built over a single key. The more distinct the key column is and the higher the overflow percentage, the less advantageous the encoded vector index becomes.

An encoded vector index can only be used to create either a temporary row number list or a temporary row number bitmap. It is also associated with a separate table probe operation in order to retrieve the table data associated with the row number selected from the encoded vector index. The main difference with the table probe associated with an encoded vector index is the paging associated with the table is no longer required to be a random synchronous I/O. The I/O can now be scheduled more efficiently to take advantage of groupings of selected rows. The effect of this more efficient scheduling is to give the appearance of skipping over larger portions of the table where no rows are to be selected.

The following example illustrates a query where the query optimizer will create a temporary row number bitmap from the encoded vector indexes to help retrieve the table data.

CREATE ENCODED VECTOR INDEX EV1 ON EMPLOYEE (WORKDEPT)
CREATE ENCODED VECTOR INDEX EV2 ON EMPLOYEE (SALARY)
CREATE ENCODED VECTOR INDEX EV3 ON EMPLOYEE (JOB)

 DECLARE C1 CURSOR FOR
  SELECT * FROM EMPLOYEE
   WHERE WORKDEPT = 'E01' AND JOB='CLERK' AND SALARY>50000
   OPTIMIZE FOR 99999 ROWS

In this example, the optimizer would choose to create and merge the temporary row number bitmaps associated with each encoded vector index. The final row number bitmap would then be used to help schedule the paging against the table for the selected rows.

The optimizer could choose to perform an index probe with a binary radix tree index if an index existed over columns WORKDEPT, JOB and SALARY. The implementation choice will probably be 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 combined key of WORKDEPT, JOB and SALARY is fairly unique), the optimizer will 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.

One of the advantages of creating encoded vector indexes, for this example, is that the three encoded vector indexes can be used by other queries in any combination. Since the binary radix tree index contains three key columns (WORKDEPT, JOB and SALARY), it is most effective only for queries that contain selection over all three key columns.

For more information, see Database Performance and Query Optimization in the Information Center Link to Information center.