During presentations for this week a member asked question if "Fetch First 1 Row Only" (FF1RO) is needed any more. It sounded strange at first but was the caller really asking if "Optimize for 1 Row" (OF1R) is still needed which seams to make more since.
In either case what dose the IBM manual state
Use OPTIMIZE FOR 1 ROW clause to influence the access path. OPTIMIZE FOR 1 ROW tells Db2 to select an access path that returns the first qualifying row quickly.
Use FETCH FIRST n ROWS ONLY clause to limit the number of rows in the result table to n rows. FETCH FIRST n ROWS ONLY has the following benefits:
-
- When you use FETCH statements to retrieve data from a result table, the fetch clause causes Db2 to retrieve only the number of rows that you need. This can have performance benefits, especially in distributed applications. If you try to execute a FETCH statement to retrieve the n+1st row, Db2 returns a +100 SQLCODE.
- When you use fetch clause in a SELECT INTO statement, you never retrieve more than one row. Using fetch clause in a SELECT INTO statement can prevent SQL errors that are caused by inadvertently selecting more than one value into a host variable.
When you specify the fetch clause but not the optimize clause, the optimize clause is implicit. When you specify FETCH FIRST nROWS ONLY and OPTIMIZE FOR m ROWS, and m is less than n, Db2 optimizes the query for m rows. If m is greater than n, Db2optimizes the query for n rows.
It seems simple but is it. Let's first look at OF1R and when you want to use it. As stated in the manual you want to use it when you know you will only get back a very limited number of rows. But what I prefer to do is, code the Select statement without it, then Explain it, then code it with the OF1R and then re-explain it, then compare them. I may even run a benchmark of each, and compare retrieval times. Then select the one performs the best in limited amount of time.
I have even seen this trick work for large result sets to force a different access path selection.
Now how about FF1RO and when should it be used. Here this can get complicated as you must know the design of the database model to properly used it. This also goes for using Distinct clause. So many time I seen the Distinct clause used as standard on every Select statement which is very bad due to the sorting and extra costs, especially when when unique result are return without it use.
So back to FF1RO. First let's start with its counter part called "Limit #" Limit is the Ansi standard and is being used with many other Database venders and if you want to write compatible SQL use Limit over FF1RO, beside it's shorter to code.
The first obvious reason is for Select Into statements when you know multiple rows return are possible to avoid a -811 sql code and you only want to process only the first row returned, meaning you won't run the same sql to process other rows. When would you not use for a select into statement is when you know that only one row can be returned, for unique SQL call. Here again to not make FF1RO a standard for all Select Into statements. To many time it is overly used because the coder does not understand the database model.
The next case to use it is during complex query using table expressions to force a early out, similar to the above when multiple rows are return but you know only one qualifying rows is need to complete that part of the complicate query.
Piece meal deletes. You will want to use the Fetch First # Rows Only (Limit #) where the # is tunable. And then loop through process until max # of records to deleted is completed or the end of the qualifying predicates returned have been deleted. Why tunable. In the loop process your first step is to get the # value from a table to set in the delete, and then once the delete is done commit and repeat the loop until all rows for the delete predicate have been deleted. This way if lockouts or time out occur while the process is running you can lower the number of rows to delete on the fly. Note if deleting all rows from a table is needed, use the truncate statement instead provide no RI is on the table row do not need to be logged.
Another purpose to use FF1RO is to use it to delete duplicate rows where only have 2 duplicate rows. But better method would be to use a Row_Number() query instead with delete instead.
There are other queries for it use which you may find out on your own.
------------------------------
Douglas Partch
------------------------------