Optimize for one row

When selecting rows for a big table in DB2, there is a clause that will retrieve the columns first. Usually DB2 will give you the result when all of the rows are selected (or when the communications buffer is full). With this clause, DB2 will give you the rows as the same time that it is retrieving them. The clause is simply: "Optimize for 1 row," as follows:

Select * from TABLE where CONDITIONS optimize for 1 row

Reader Feedback

Anonymous writes: "It is true that DB2 supports this clause, but the writer's understanding of the clause is inaccurate. When this clause is appended to a DB2 SQL statement it tells DB2 to "optimize" the statement as if only one row will be returned. It has NOTHING to do with the execution of the statement (other than, of course, running the statement using a different access path than would "normally" have been formulated by the optimization process). A good usage of this clause is to coerce DB2 into avoiding list prefetch in an access path for an online transaction. List prefetch causes DB2 to accumulate the record IDs (RIDs) of each row to be returned. The RIDs are then sorted so that DB2 can avoid re-reading pages -- if the RIDs are sorted, then DB2 will read all records that exist on a single page at the same time. This can save on I/O. However, for online transactions it can cause the online user to have to wait an inordinately long time for the first page/screen to appear (waiting for DB2 to grab and sort the RIDs). Without list prefetch the first page/screen usually will appear much quicker (even though DB2 may have to re-read pages to process the entire SQL result set). It is also important to note that the OPTIMIZE FOR clause does not limit the number of rows that can be fetched or affect the result in any way other than performance. In general, if you are retrieving only a few rows, you can use OPTIMIZE FOR 1 ROW to influence the access path that DB2 selects."

R.M. writes: "The basic problem is that this tip is inaccurate. Every sentence except the last has errors, and the focus on a single technique, rather than on determining the real reason, is more likely to cause problems than to give help. There is no simple rewrite, but there are several sources of more accurate information:

• Bonnie Baker's column in DB2 Magazine
• DB2 for OS/390 support page: hundreds of FAQs, tips, & hints

For More Information

• What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
• The Best IBM DB2 Web Links: tips, tutorials, scripts, and more.
• Have a DB2 tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical DB2 questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our DB2 gurus are waiting to answer your technical questions.