In the following cases, the retrieval performance may not improve, or conversely, may become worse, even if the retrieve first n records facility is used.

1. If the sum of the offset of the first row to return and the maximum number of rows to return is the same or extremely close to the value when the LIMIT clause is not specified.
2. If the LIMIT clause is specified but the ORDER BY clause is not, HiRDB cannot uniquely determine which rows are to be retrieved. The ORDER BY clause should therefore be specified whenever the LIMIT clause is specified. However, when the ORDER BY clause is specified, the SQL optimization method may select a different access path and the retrieval performance may worsen. To check the access path selected by the SQL optimization method, use the access path display utility (pdvwopt).
3. If both the ORDER BY and LIMIT clauses are specified and there are several rows that have the same sort key value as the last row that was skipped based on the offset of the first row to return or the last row that was obtained based on the maximum number of rows to return, HiRDB cannot uniquely determine which of the rows with the same sort key value are to be retrieved. To retrieve a specific row that has the same sort key value as the row that satisfies this condition, add more columns to the sort key. However, when more sort key columns are added, the SQL optimization method may select a different access path, and the retrieval performance may worsen. To check the access path selected by the SQL optimization method, use the access path display utility (pdvwopt).

In cases like those described, do not use the retrieve first n records facility.

If the maximum number of rows to return is 1 or more and the sum of the offset of the first row to return and the maximum number of rows to return is 32,767 or less, HiRDB stores the rows that fall within that sum in memory instead of creating a work table. Therefore, the required memory size increases compared to when the facility is not used. For details about the required memory size, see Calculating the required memory size for execution of the retrieve first n records facility in the HiRDB Version 8 Installation and Design Guide.