To make to DB2 optimizer choose the correct access path I have used many different tricks through the years. I have also published some of them here on my website. Some of the tricks are recommended by IBM and some are certainly not. One of the recommended tricks are to use OPTIMIZE FOR n ROWS. Until recently I had ignored this trick as useless, but now I am a lot wiser.

OPTIMIZE FOR n ROWS is written at the very bottom of your SQL statement. The most exciting use of OPTIMIZE FOR n ROWS is to use n = 1. If I have an ill behaving SQL statement I start my optimisation process by appending OPTIMIZE FOR 1 ROWS and look at the resulting access path. OPTIMIZE FOR 1 ROWS forces the optimizer to choose an access path that returns the first row as quickly as possible. Therefore OPTIMIZE FOR 1 ROWS prefers nested loop joins instead of other join methods. In some cases tablespace scan are preferred instead of index access just so you are warned.

If you for instance have a program with a SQL cursor returning a large number of rows it may take some time before your program starts fetching through the result set. This happens when DB2 chooses to sort the result even though an index could have been used. Such programs can be hard to get to work properly with restart because each new restart requires the whole result set to be created before the fetch process will be kicked off. In such cases an OPTIMIZE FOR 1 ROWS may work wonders.

If your installation uses DB2 governor to limit resource consumption of long running SQL statements then you can use OPTIMIZE FOR 1 ROWS in order to prevent your SQL statement from getting a SQLCODE of -905. Here is the trick that DB2 governor only measures each individual call to DB2 and not the SQL statement as a whole. When you execute a SQL statement using SPUFI, QMF or other tools alike, then the execution is split up into a series of calls to DB2. First an OPEN CURSOR is performed and afterwards a FETCH for each row and each of these calls are measured separately by DB2 governor.

I must strongly emphasize that OPTIMIZE FOR 1 ROWS is not a miracle cure just like other DB2 tuning recommendations. If might well be that a nested loop join is much slower than one of the other join methods when all the individual DB2 calls for a total retrieval of the result set are summed up. And an OPTIMIZE FOR 1 ROWS cannot eliminate a sort if your ORDER BY clause contains non indexed columns or the sort columns are in another order than in the index.