- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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.
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.
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.
- 8085 Program to find Square of a number using look up table
- Program to find the Square of a number using a look-up table in 8085 Microprocessor
- Differentiate between direct cost and indirect cost.
- Difference Between Direct and Indirect Addressing Modes
- Difference between Alternating Current and Direct Current
- Concept of Direct Memory Access (DMA)
- Operating System Design and Implementation
- Direct Arc Furnace: Types, Electrical and Mechanical Requirements
- Explain the design and implementation of a simple microsequencer?
- What is the purpose of a register in a CPU and what are special purpose registers?
- Python - Implementation of Polynomial Regression
- Implementation of connection-oriented services
- Purpose and usage of SAVEPOINT in COBOL-DB2 program