# Implementation and purpose of direct index look-up

DB2DatabaseBig Data AnalyticsCOBOL

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.

 ORDER_ID ORDER_DATE ORDER_TOTAL Z22345 30-10-2020 342 Z33412 14-08-2020 543 Z56990 19-10-2020 431 Z56902 21-09-2020 6743 Z99781 04-11-2020 443 Z56112 29-08-2020 889

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.

 ORDER_ID ORDER_DATE Z33412 14-08-2020

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.

Published on 30-Nov-2020 09:38:09