- 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
What is the purpose of OPTIMIZE FOR ROWS in DB2 SQLs? How is it useful?
The OPTIMIZE FOR N ROWS is a DB2 clause which we can add in the query to give priority for the retrieval of the first few rows only. This clause will enable the optimizer to choose the access path that minimizes the response time for fetching first few rows.
The OPTIMIZE FOR N ROWS clause is not effective on SELECT DISTINCT and COUNT function because DB2 will need the entire qualifying rows in order to fetch the DISTINCT rows or COUNT the number of rows. The OPTIMIZE FOR N ROWS clause gives DB2 a better opportunity to establish the access path.
The OPTIMIZE FOR N rows clause can be used in a SQL query as given below.
SELECT ORDER_ID, ORDER_TOTAL FROM ORDERS ORDER BY ORDER_TOTAL DESC OPTIMIZE FOR 2 ROWS
We will use “FETCH FIRST n ROWS ONLY” to limit the number of rows returned and it will not consider the actual number of qualifying rows.
A practical scenario in which we can use OPTIMIZE FOR N ROWS would be a CICS screen where we can display a list of 5 orders only.
SELECT ORDER_ID, ORDER_TOTAL FROM ORDERS ORDER BY ORDER_TOTAL DESC OPTIMIZE FOR 5 ROWS
For example, our DB2 ORDERS table has the below data.
The result of our OPTIMIZE FOR 5 ROWS query will be as given below.
- What is AAA rating and how is it useful?
- What is an Account Number and how is it useful?
- How to use SQLCA in a COBOL-DB2 program? What is the purpose of SQLCA?
- What is the purpose and usage of “FOR UPDATE OF” clause in a COBOL-DB2 program
- What is Light Based Key Distribution System and how is it useful?
- What is the purpose of using MySQL CHAR_LENGTH() function? Which function is the synonym of it?
- What is the purpose and usage of SQLCODE within the SQLCA in a COBOL-DB2 program
- How cow dung is useful for agriculture?
- What is the purpose of System Programs?
- What is the purpose of System Calls?
- What is the purpose of Human Life?
- What is the purpose of any research?
- What is the purpose of Risk Management?
- What is the usage and purpose of DCLGEN and host variables used in COBOL-DB2 program
- What is the purpose of interfaces in java?