Usage and example of Multi-index and Index-only access path in DB2

Problem: Explain the difference between Multi-index access and Index-only access paths used by DB2 Optimizer. Give an example for both.

Solution

The Index-only access and Multi-index access are the types of access path which the DB2 optimizer chooses in order to fetch the query results. The Index-only access paths are used when all the columns given in the SELECT query are present in the index. In this case, the optimizer does not have to go to the data page to fetch the result, all the data is available in the index page.

For example, the ORDERS table has a primary key as ORDER_ID then for the below SQL query, the DB2 optimizer will choose index only access.

Example

SELECT ORDER_ID FROM ORDERS.

The Multi-index access is the access path which is chosen by the DB2 optimizer when the WHERE clause of the SQL query has predicate on two or more indexes. In this case, the result from indexspace/dataspace is extracted for each of the indexes (column in the WHERE clause) and then the result is joined as per the logical operator used in the WHERE clause (AND, OR).

For example, if the ORDERS DB2 table is having a primary key on ORDER_ID and a unique index built on INVOICE_ID. The below SQL query will take a Multi-index access path.

Example

SELECT ORDER_ID, INVOICE_ID FROM ORDERS WHERE
   ORDER_ID IN (‘Z33476’, ’Z11674’, ‘Z88901’) AND
   INVOICE_ID IN (‘A19908’, ‘A90771’)