Fetch for Limited Rows During cursor processing, the ability to tell DB2 to only fetch the first or top n rows is a requirement in many applications. There are ways in SQL and the application program to do this; however, they are not the most efficient. For example, a query could be written as follows : EXEC SQL SELECT * FROM EMPLOYEE A WHERE 5 >= (SELECT COUNT(*) FROM EMPLOYEE B WHERE B.EMPNO <= A.EMPNO) END-EXEC. However, this results in terrible performance, because DB2 will have to read rows proportional to square of the numbers of table rows. With the FETCH FIRST clause of a fullselect, DB2 has this functionality with improved performance, as the clause sets a maximum number of rows that can be retrieved through a cursor. The application specifies that it does not want to retrieve more than some fixed number of rows, regardless of how many rows qualified for the result set. FETCH FIRST and OPTIMIZE FORThe OPTIMIZE FOR n ROWS clause, which provides additional information to the optimizer about the intent, is different from the FETCH FIRST clause. The OPTIMIZE FOR clause could allow the entire answer set to be retrieved. In contrast, the FETCH FIRST n ROWS ONLY stops processing after the specified number of rows. This provides direct control to the application programmer over the size of the result table. For example, an implementation limit on the number of rows displayed for an online screen can now be enforced. The following example shows the use of the FETCH FIRST n ROWS ONLY clause with the OPTIMIZE FOR n ROWS. EXEC SQL DECLARE FIRST_FIVE CURSOR FOR SELECT EMPNO FROM EMPLOYEE ORDER BY EMPNO FETCH FIRST 5 ROWS ONLY OPTIMIZE FOR 5 ROWS END-EXEC. Using FETCH FIRST for Existence CheckingThe FETCH FIRST clause is valid for singleton SELECTs, whereas the OPTIMIZE clause is not. This makes the FETCH FIRST clause a perfect solution for optimal existence checking. It will perform a maximum of only one fetch to determine existence, and there is no internal second fetch for the 811 return code required. It automatically implies OPTIMIZE FOR 1 ROW and therefore discourages sequential prefetch, multi-index access and list prefetch. This technique requires minimal coding to support singleton select, and no cursor logic is required. It can also be coded within an application program or dynamically (Query Management Facility, DSNTEP2, etc.). The following is an example of FETCH FIRST for existence checking: EXEC SQL SELECT 1 INTO :hv-check FROM TABLE WHERE COL1 = :hv1 FETCH FIRST 1 ROW ONLY END-EXEC. |
Team-Fly |
Top |