- 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
How to find out the access path selected by optimizer for a particular query?
DB2 optimizer plays an important role in the overall performance of the database. The optimizer selects the optimal access path for each query through which data can be fetched from the database. It identifies the indexes to follow, query predicates, etc.
The optimizer selects the access path automatically and we can easily find the access path using EXPLAIN DB2 command. We have to SET the query number first and then give our SQL query to find out its access path in three simple steps.
We have a DB2 ORDERS table and we want to examine the SELECT query which has a predicate on the ORDER_DATE column.
Step 1: Execute below EXPLAIN command and set the query number followed by SELECT query ad below.
EXPLAIN PLAN SET QUERYNO=23445 FOR SELECT ORDER_ID FROM ORDERS WHERE ORDER_DATE = ‘12-08-2020’
Step 2: The above EXPLAIN command will update the PLAN table with relevant query and explain details. Execute below command to view details in PLAN table.
SELECT * FROM PLAN_TABLE WHERE QUERYNO = 23445
Step 3: The result of the above query will give below details.
The ACCESTYPE column has the details of access methods like Index scan, Index only scan, etc. The MATCHCOLS column stores the number of index keys utilized for index scan. The ACCESSNAME column stores the name of the index used for index scan and INDEXONLY column tells us whether data can be fetched from the index itself.
- How to find the access path selected by an optimizer for a SQL statement used in a DB2 program?
- How can we find out the storage engine used for a particular table in MySQL?
- C++ program to find out the shortest cost path in a given graph for q queries
- Program to find out the shortest path to reach the goal in Python
- C++ Program to find out the number of jumps needed for a robot to reach a particular cell in a grid
- How can I query for all the tables having a particular column name?
- How to find out all the indexes for a DB2 table TAB1?
- Program to find out the minimum path to deliver all letters in Python
- How to select a query for a selected day(2010-11-04) to current date using MySQL?
- How to find the row mean for selected columns in R data frame?
- Program to find out the letter at a particular index in a synthesized string in python
- MongoDB query to update selected fields
- C++ Program to find out the number of cells to block in a grid to create a path
- How will you find out all the indexes which are built in a particular DB2 table?
- Program to find out the minimal cost so that the citizens have access to a market in Python