Implementation and purpose of direct index look-up

The direct index look-up is chosen by the DB2 optimizer when all the columns used in the predicate of the WHERE clause is part of the index.

For example, if we have ORDERS DB2 table as below.

In this table, there is one index which is built having columns named ORDER_ID and ORDER_DATE. For the below query, DB2 optimizer will choose direct index look-up because the columns used in the SELECT statement are also part of the index.

Example

SELECT ORDER_ID, ORDER_DATE, INVOICE_ID FROM ORDERS
   WHERE ORDER_ID = ‘Z33412’ AND ORDER_DATE = ‘14-08-2020’

The result of the above query will be as follows.

In the above query the WHERE clause has predicates on both the columns which are part of the primary key, so index for them should already be there. In this case, the optimizer can use these indexes to look in index-space and retrieve the corresponding data (INVOICE_ID in this case) from the data-space.

This type of access method is very fast as compared to other access methods as the optimizer does not have to look for the required data in data pages. All the information is already available in index pages which reduces the SQL cost significantly.

Ideally, these types of queries are only used when we have to check only the existence of row/data in the table.