Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
| 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.
113 Views
