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
Usage and example of Multi-index and Index-only access path in DB2
Problem: Explain the difference between Multi-index access and Index-only access paths used by DB2 Optimizer. Give an example for both.
Solution
The Index-only access and Multi-index access are the types of access path which the DB2 optimizer chooses in order to fetch the query results. The Index-only access paths are used when all the columns given in the SELECT query are present in the index. In this case, the optimizer does not have to go to the data page to fetch the result, all the data is available in the index page.
For example, the ORDERS table has a primary key as ORDER_ID then for the below SQL query, the DB2 optimizer will choose index only access.
Example
SELECT ORDER_ID FROM ORDERS.
The Multi-index access is the access path which is chosen by the DB2 optimizer when the WHERE clause of the SQL query has predicate on two or more indexes. In this case, the result from indexspace/dataspace is extracted for each of the indexes (column in the WHERE clause) and then the result is joined as per the logical operator used in the WHERE clause (AND, OR).
For example, if the ORDERS DB2 table is having a primary key on ORDER_ID and a unique index built on INVOICE_ID. The below SQL query will take a Multi-index access path.
Example
SELECT ORDER_ID, INVOICE_ID FROM ORDERS WHERE ORDER_ID IN (‘Z33476’, ’Z11674’, ‘Z88901’) AND INVOICE_ID IN (‘A19908’, ‘A90771’)
178 Views
