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.

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