Hello friends,

/*I've pasted this question in Oracle Administration as well. Hopefully it will be answered somewhere.*/

Is there a gain in performance when executing a query restricted by rownums? For example, I would like to create a procedure that would return 10 records (similar to altavista paging, or so).

I have the following query:

SELECT *
FROM table1
WHERE conditon AND rownum>N AND rownum
What I would like to know - when this query is executed, does Oracle first select all rows, and then returns the needed records, or it uses some optimization to select the needed rows without selecting all rows that satisfy the condition.

If there isn't much gain using such a query, It is probably better to write some 3GL caching system.

Has anyone coded such a system before?
I will appreciate it very much if you share your ideas.

Thank you.