EVI Only Access (EOA)

Related resource: IBM developerWorks Article: https://ibm.biz/DB2foriEOA

If you're a DB2 for i performance analyst, database engineer, or simply someone who owns the indexing strategy, Encoded Vector Indexes (EVIs) are improved to be usable by SQE for projection of columns.

If you've never heard of EVIs, read this white paper and attend this workshop:

• indexStrategyWhitePaper.pdf
• http://ibm.biz/SQLPerfWorkshop

EVIs traditionally focus on selection keys (WHERE clause) and aggregation.  With this enhancement, EVIs can be used for projection (SELECT list) of column values. This new approach to leveraging EVIs can be observed through the Index Advisor where REASON_TYPE will be set to 'I8'.

To see an example of the performance benefit, we examined the impact of the having an EVI over fld1 and another EVI over fld2 for the following query. This example shows a query that would achieve the most benefit from this combined EVI implementation, so your results will vary greatly.  Database performance and indexing techniques are complex, consult our team of experts as needed.

SELECT COUNT(DISTINCT(fld1)) WHERE fld2 < 'value'

Figure 1. Comparison of EVI vs non-EVI data access for one query in a controlled environment.

A second example of the potential performance benefits of EOA are represented in Figure 2.  The SAP BW-EML benchmark showed a measured performance improvement when additional EVIs existed to assist with the performance of projecting column values back to the caller.  IBM is working with the SAP team to amend their recommended set of EVIs to accommodate for this new use of EVIs.

Figure 2. SAP BW-EML Benchmark