use_rownum

[ORACLE]
use_rownum=0

The value is the maximum number of rows to be returned by a single select.

use_rownum controls whether SQL select statements generated by AUXORACL limit the number of rows to be returned by modifying the generated select statement to be SELECT * FROM (select_statement) WHERE ROWNUM<=n". This useful facility was introduced in Oracle Version 8.1. There is no elegant means in standard SQL of limiting the number or rows to be returned by a SQL SELECT.

This facility can only be used with Oracle 8.1 (and presumably later versions). It is not supported by Oracle 8.

Using this facility can help performance when accessing a large table. For example usually a DP4 fetch(FIRST) will read the entire table. Although subsequent calls to fetch(NEXT) will be fast, in the case where only the FIRST record is required the performance hit may be unacceptable, particularly on very large databases. Using use_rownum=1 makes the fetch FIRST faster. On the other hand a subsequent sequenece of fetch(NEXT) operations will be rather slower. You need to bear in mind the following:

• The time taken for a select statement is dependent on a combination of the number of rows that need to be examined, and the number of rows that need to be placed in the result set. Limiting the number of rows only improves the second component.
• If you specify a value for use_rownum grater than 1 then in many cases subsequent fetches on a table can reuse an earlier result set. Such fetches are very fast compared with fetches that need to execute a select statement.

A value of use_rownum between 100 and 1000 will probably give quite good results.

It would be useful if rownum could be used as a simple additional predicate. Unfortunately testing has indicated that this does not work reliably, and that Oracle will not always return rows in the expected order.