Setting a fetch-limit is an alternative to specifying a LIMIT clause explicitly in your SELECT query, with an important difference in how the query will be executed, which can affect performance. fetch-limit is an ACT feature that controls how many rows are fetched by the queen from the workers and by the client from the queen. ACT stops fetching results from the cursor after the specified limit is reached. LIMIT is a SQL clause that controls how many rows are computed on the worker(s) or the queen.
The following examples illustrate the difference between using fetch-limit and using a LIMIT clause in the SQL query:
Consider the statement:
SELECT * FROM FOO, BAR where FOO.A = BAR.A;