/*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:
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.
I agree with one of your post when you sugested not to post the same question to two different forums. It realy is confusing.
About your solution - I agree it is more efficient in some rare situations. However in a real life requirenments, when sliding window mechanism has to be applied to a *sorted* total resultset your solution is wrong because ROWNUM and ORDER BY in the same selct doesn't work as one could expect (and I know you are perfectly aware of that, it must just have slipped your mind). I'll explain what I mean in more detaile in Administartion forum under your solution.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?