How the EVI works

EVIs work in different ways for costing and implementation.

For costing, the optimizer uses the symbol table to collect metadata information about the query.

For implementation, the optimizer can use the EVI in one of the following ways:

• Selection (WHERE clause)

  The database engine uses the vector to build a dynamic bitmap or list of selected row ids. The bitmap or list contains 1 bit for each row in the table. The bit is turned on for each selected row. Like a bitmap index, these intermediate dynamic bitmaps (or lists) can be ANDed and ORed together to satisfy a query.

  For example, a user wants to see sales data for a specific region and time period. You can define an EVI over the region and quarter columns of the table. When the query runs, the database engine builds dynamic bitmaps using the two EVIs. The bitmaps are ANDed together to produce a single bitmap containing only the relevant rows for both selection criteria.

  This ANDing capability drastically reduces the number of rows that the system must read and test. The dynamic bitmaps exists only as long as the query is executing. Once the query is completed, the dynamic bitmaps are eliminated.

• Grouping or Distinct

  The symbol table within the EVI contains distinct values for the specified columns in the key definition. The symbol table also contains a count of the number of records in the base table that have each distinct value. Queries involving grouping or distinct, based solely on columns in the key, are candidates for a technique that uses the symbol table directly to determine the query result.

  The symbol table contains only the key values and their associated counts, unless INCLUDE is specified. Therefore, queries involving column function COUNT are eligible for this technique. But queries with column functions MIN or MAX on other non-key columns are not eligible. MIN and MAX values are not stored in the symbol table.

• EVI INCLUDE aggregates

  Including additional aggregate values further extends the ability of the symbol table to provide ready-made results. Aggregate data is grouped by the specified columns in the key definition. Therefore, aggregate data must be over columns in the table other than those columns specified as EVI key values.

  For performance, these included aggregates are limited to numeric results (SUM, COUNT, AVG, VARIANCE) as they can be maintained directly from the inserted or removed row.

  MIN or MAX values would occasionally require other row comparisons during maintenance and therefore are not supported with the INCLUDE keyword.

  EVI symbol table only access is used to satisfy distinct or grouping requests when the query is run with commitment control *NONE or *CHG.

  INCLUDE for additional aggregate values can be used in join queries. When possible, the existence of EVIs with INCLUDE aggregates causes the group by process to be pushed down to each table as necessary. See the following EVI INCLUDE grouping push down example: EVI INCLUDE aggregate example