Let’s start this discussion candidly – I am a big fan of the FETCH FIRST n ROWS ONLY clause coded in a SQL statement. That assertion will require some clarification, since there are some valid use cases, and others that make little sense. Regardless, I will try not to over-complicate the discussion.
Simply put – whenever the application needs fewer rows than qualify from the query, or if there is an upper limit on the number of rows to be returned to the application, then including the FETCH FIRST n ROWS ONLY clause on the SQL statement makes sense (where n indicates the maximum number of desired rows).
If your SQL statement could qualify 10,000 rows (that is – you fetched all of the rows that qualified from the WHERE clause predicates), but you only want 100 rows, then specify FETCH FIRST 100 ROWS ONLY. If you only want 1 row, then specify FETCH FIRST 1 ROW ONLY.
If you think about this logically, this provides valuable information for DB2 in how to optimize your query. If your query does not contain the FETCH FIRST clause and you open a cursor, fetch 10 rows in the application and close the cursor – DB2 does not know how many fetches you performed in your application code, it is only attempting to optimize each individual SQL statement based upon the SQL text. This may be a revelation to application programmers and some DBAs that DB2 is not inspecting the application code. Coding FETCH FIRST in the query does provide this knowledge to DB2.
So what if a query qualifies 10,000 rows, is there any benefit to using a FETCH FIRST value larger than the number of qualified rows – such as FETCH FIRST 99999 ROWS? Typically the answer is no – unless other executions of the same query would qualify more than the FETCH FIRST value. The exception to this is if the usage of FETCH FIRST allows the in-memory replacement sort to be exploited (more on that later).
Result returned from FETCH FIRST vs OPTIMIZE FOR clauses
How does FETCH FIRST 100 ROWS differ from OPTIMIZE FOR 100 ROWS?
OPTIMIZE FOR 100 ROWS tells the optimizer to “optimize” for that number. But if you specify OPTIMIZE FOR 100 ROWS, DB2 does not limit how many rows you can fetch – if your query qualifies 10,000 rows and you specify OPTIMIZE FOR 100 ROWS – DB2 will allow you to fetch the 101st row, the 102nd, and continue until no more rows qualify (SQLCODE=+100).
Coding FETCH FIRST 100 ROWS ONLY places a limit on the number of rows returned at 100. If your query could qualify 10,000 rows and you code FETCH FIRST 100 ROWS ONLY, then when you attempt to fetch the 101st row, no further rows will be returned (SQLCODE+100).
FETCH FIRST vs OPTIMIZE FOR clauses for access path selection
For the choice of access path (that is, informing the optimizer to optimize for 100 rows in the prior examples), then both clauses are the same and can be used together or independently. For optimization purposes, DB2 will use the OPTIMIZE FOR value if it exists, otherwise if only FETCH FIRST is coded, then its value is also used to imply OPTIMIZE FOR. If you are wondering if it is possible to specify different values for these? Yes it is possible. The next question is whether it makes sense to do so?
Remember, the major goal of FETCH FIRST is to place a limit on the number of rows that can be returned to the application. And using OPTIMIZE FOR n ROWS with FETCH FIRST is only necessary if a desired access path is not chosen with FETCH FIRST alone. One (1) is the magic number to remember, since FETCH FIRST 1 ROW ONLY or OPTIMIZE FOR 1 ROW can strongly encourage the optimizer to choose an access path that avoids a sort. Other values (greater than 1) for FETCH FIRST or OPTIMIZE can still provide encouragement to the optimizer to avoid a sort where possible – but this “encouragement” is not as strong as FETCH FIRST 1 ROW or OPTIMIZE FOR 1 ROW. Therefore, if you code FETCH FIRST 100 ROWS ONLY and you wanted a sort avoidance plan (but didn’t initially achieve that), then try adding OPTIMIZE FOR 1 ROW. If you code FETCH FIRST 1 ROW ONLY (because you truly only want 1 row) and a sort avoidance plan was chosen that was sub-optimal, then try adding OPTIMIZE FOR 2 ROWS. Any other variations of different values for these clauses are less common and unlikely to achieve repeatable (or logically explainable) behavior.
Performance benefits to FETCH FIRST
When the access path for a SQL statement is such that there are no sorts or materializations (where an intermediate result is written to a temporary workfile), then there may not be a performance benefit to coding FETCH FIRST. When there are sorts or materialization, then there may be significant performance improvements available depending on how large the number of qualified rows are compared to how small the value for FETCH FIRST.
DB2 began optimizing sort performance for FETCH FIRST beginning in DB2 9, and figures 1 and 2 will demonstrate this initial optimization.
Figure 1: SQL with sort and number of fetches known only to the application.
The example in Figure 1 is depicting a SQL statement requiring a sort for the ORDER BY, and the application only fetches 3 rows from the sorted result. Although the example contains only 10 rows, the bubble to the right asks the question – what if this was 1 million rows? And the answer would be that sort would need to write the intermediate results to a workfile – including all 1 million rows, since DB2 has no knowledge that anything other than the full 1 million rows are required (in sorted order) by the application.
Figure 2: Sort with FETCH FIRST.
In figure 2, the query contains the ORDER BY C1 and FETCH FIRST 3 ROWS ONLY clause. The optimization depicted was delivered in DB2 9 for z/OS and is referred to as the “in-memory replacement sort”. Instead of sorting all 10 rows (or all 1 million rows) - as the rows are being scanned, DB2 only keeps track (in-memory) of the top 3 rows (since this query requested FETCH FIRST 3 ROWS ONLY). The red arrows identify from left to right which rows are kept in-memory and finally which rows are returned to the application. As the data is scanned, the top 3 rows are initially the 1st 3 rows read (9, 6 & 4). When value 1 is read, it replaces 9, then 3 replaces 6, and 2 replaces 4, and finally values 1, 2 & 3 are returned as the top 3 rows in order (as requested by the SQL).
This in-memory replacement sort is only used when a sort is required for a query with FETCH FIRST, and that result can be contained in-memory. Unlike other DB2 sorts, this in-memory replacement sort cannot spill to disk and thus must guarantee to fit in-memory. Note that FETCH FIRST guarantees the maximum number of rows to be returned, and the required amount of memory is known based upon the total length of the columns selected (and sorted) and the FETCH FIRST value. This in-memory technique typically outperforms regular DB2 sort when fewer than 1000 rows are required, and DB2 (currently) dictates the memory allocation for this sort at 128K of memory.
In DB2 11, there are additional optimizations for sort with FETCH FIRST even if the sort cannot be contained in-memory. For very large number of qualified rows, sort may need numerous passes (known as merge passes) to sort the entire result. In such cases with FETCH FIRST, each intermediate sort phase can limit the internal result to the value of FETCH FIRST. What this means is that if you are sorting 100 million rows, there is a benefit to knowing that the application only wants the top 1000 or 10,000 (or even 1 million) rows.
DB2 12 for z/OS delivers numerous additional performance optimizations for sort (and sort avoidance) with FETCH FIRST.
DB2 12 for z/OS optimizations for FETCH FIRST
The first enhancement for FETCH FIRST in DB2 12 is the ability to push down ORDER BY and FETCH FIRST into materialized query blocks.
Figure 3. Push-down of ORDER BY and FETCH FIRST
The example in Figure 3 shows a view definition containing three tables combined with UNION ALL, and then a SELECT from that view with a query that includes ORDER BY and FETCH FIRST clauses. Prior to DB2 12, the ORDER BY and FETCH FIRST would be applied to all rows returned from the view. As depicted in Figure 3, DB2 12 will push the ORDER BY and FETCH FIRST into each query block which will allow the in-memory replacement technique to be exploited separately for each sort or usage of an index to avoid a sort if such an index is available.
The ability to reduce the size of the sort for each UNION ALL leg, or for the entire query, is not possible without the FETCH FIRST clause on the query example in Figure 3.
The second related enhancement in DB2 12 can reduce the number of rows fetched into sort if an index provides order for the leading column(s) of the sort, as depicted in Figure 4.
Figure 4 Partial order with FETCH FIRST.
The example in Figure 4 is a simple SELECT with ORDER BY and FETCH FIRST. For DB2 to completely avoid a sort for order, the optimizer must choose an available index with columns matching the ORDER BY clause. In this example, the ORDER BY is on columns C1, C2, but there only exists an index on C1 – thus, sort is required for order. Prior to DB2 12, all rows qualifying from the query would be sorted, and the top 10 rows fetched (given the FETCH FIRST 10 ROWS ONLY). But in DB2 12, the optimizer can recognize that by choosing the index on C1, order will be provided into sort by that column. Therefore, once 10 rows are fetched into sort, then DB2 12 knows to only fetch until the next change in the C1 value – in Figure 4, once the 10th row is fetched, then DB2 will keep fetching until the 13th row when C1 changes from values 4 to 5. Sort only needs to sort the top 12 rows into C1, C2 order to return the top 10 rows.
This optimization is not available in DB2 without the FETCH FIRST clause in the query.
Other (known) optimizations with FETCH FIRST
The FETCH FIRST clause has been available for many releases of DB2, but it is likely that many of the aforementioned performance enhancements associated with this clause are unknown to most users. There are numerous other performance optimizations that may be well known by DB2 users.
Firstly, when you specify FETCH FIRST in a distributed query, DB2 may exploit fast implicit close to improve performance. Fast implicit close is the process of DB2 closing a cursor after fetching the nth row or when no more rows are to be returned (SQLCODE=+100). This can improve performance by saving an additional network transmission between the client and server for a separate close to be issued.
Another known optimization of FETCH FIRST, or more accurately, a common usage of FETCH FIRST that can improve performance – is FETCH FIRST 1 ROW ONLY in a singleton select as shown in Figure 5.
Figure 5. FETCH FIRST in a singleton select.
If a singleton select is used to check/confirm that at least 1 row exists, then FETCH FIRST can be used to improve the efficiency of the SELECT statement. If the SQL statement is guaranteed to return 1 row (because there are equals predicates matching on all columns of a unique index), then FETCH FIRST is not necessary. However, the examples in Figure 5 demonstrate a scenario where performance can be improved with FETCH FIRST 1 ROW ONLY added to the select statement. The query on the left will fetch the 1st qualified row, and then DB2 will look for the 2nd qualified row to determine if the SQLCODE=0 or -811 (error indicating more than 1 row qualified for a singleton select). Since the query only cares whether 1 or more rows exists, then adding FETCH FIRST 1 ROW ONLY to the query on the right means that DB2 will stop once the 1st row is retrieved, and will not look to see if a 2nd row exists. This can improve performance for such singleton selects that only require 1 row to be returned.
Another known (and documented) use case for FETCH FIRST is to code this in subqueries where a limited number of rows are needed. This is not necessary for subqueries with column functions such as MAX, MIN, COUNT etc, and the use case is interesting but limited.
Summary and future optimizations for FETCH FIRST
The goal here was to highlight the potential performance benefit of coding FETCH FIRST in a SQL statement when the application required a subset of the qualified rows. And a complementary scenario is when the application sets a maximum upper limit on the number of rows that can be retrieved. In such a scenario, if the application does need to fetch past that upper limit, then a discussion of cursor scrolling or pagination to fetch the subsequent rows in the next query execution is best kept for another day.
And while we outlined some of the optimizations for reducing sort and materialization overhead in recent releases of DB2 with FETCH FIRST, you can expect to see additional optimizations in future that exploit DB2’s knowledge of the maximum number of rows that can be returned from the query.
Biography: Terry Purcell is the lead designer for the DB2 for z/OS Optimizer at IBM's Silicon Valley Lab, and is recognized worldwide for his expertise in query optimization and performance with DB2 for z/OS.