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 may use the EVI in one of the following ways:

• Selection (WHERE clause)

  If the optimizer decides to use an EVI to process the query, the database engine uses the vector to build the dynamic bitmap (or a list of selected row ids) that contains one bit for each row in the table, the bit being turned on for each selected row. Like a bitmap index, these intermediate dynamic bitmaps (or lists) can be AND'ed and OR'ed together to satisfy an ad hoc query.

  For example, if a user wants to see sales data for a certain region during a certain time period, you can define an EVI over the region column and the Quarter column of the database. When the query runs, the database engine builds dynamic bitmaps using the two EVIs and then ANDs the bitmaps together to produce a bitmap that contains only the relevant rows for both selection criteria. This AND'ing capability drastically reduces the number of rows that the server must read and test. The dynamic bitmap(s) exists only as long as the query is executing. Once the query is completed, the dynamic bitmap(s) are eliminated.

• Grouping or Distinct

  The symbol table within the EVI contains the distinct values for the specified columns in the key definition, along with a count of the number of records in the base table that have each distinct value. The symbol table in effect contains the grouping results of the columns in that key. Therefore, queries involving grouping or distinct on the columns in that key are potential candidates for a technique that uses the symbol table directly to determine the query result. Note that the symbol table contains only the key values and their associated counts. Therefore, queries involving column function COUNT are eligible for this technique, but queries involving column functions MIN or MAX on other columns are not eligible (since the min and max values are not stored in the symbol table).

 

Parent topic:

Encoded vector indexes