The optimizer does not know how much data you are going to fetch from the query. DB2 uses the system catalog statistics to estimate the number of rows that are returned if the entire query is processed by the application. However, if you are not going to read all the rows of the result set, use the OPTIMIZE FOR n ROWS clause, where n is the number of rows you intend to fetch.
The OPTIMIZE FOR clause lets you tell DB2 how many rows you intend to process. DB2 can then make access path decisions to determine the most efficient way to access the data for that quantity. The use of this clause discourages such actions as the list prefetch, sequential prefetch, and multi-index access. It encourages index usage to avoid a sort, and a join method of the nested loop join. A value of 1 is the strongest influence on these factors.
Insert the number of rows you intend to fetch in the OPTIMIZE FOR clause. Incorrectly representing the number of rows you intend to fetch can result in a poorly performing query.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|