DB2 - Problem description
Problem IT37607 | Status: Closed |
OPTIMIZE FOR N ROWS CLAUSE CAUSES THE QUERY TO SCAN THE SUB-OPTIMAL PLAN | |
product: | |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 | |
Problem description: | |
When both FFNR and OFNR is specified , optimizer can pick a sub-optimial plan with a few exceptions, optimizer automatically optimize for N rows, if fetch first N rows is specified. So "optimize for N row" clause is not necessary, and it exposes the costing bug. The few exceptions are: when the select clause contains distinct, OLAP functions, or aggregate functions (group by) | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * There is no resolution. The issue is a limitation of the * * product, introduced in v95. * **************************************************************** | |
Local Fix: | |
1)remove the "optimize for N row" clause. Since v95, (2) or Suggest to add an index | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * There is no resolution. The issue is a limitation of the * * product, introduced in v95. * **************************************************************** | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 13.07.2021 17.05.2022 17.05.2022 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |