What happens when limiting an oracle subquery

A friend of mine and I recently were having a debate about this, so I was wondering if anyone here might actually know the answer.

Generally, to emulate the LIMIT start_index,result_count syntax of MySQL and the LIMIT result_count OFFSET start_index functionality of MySQL and PostgreSQL in Oracle, we use:

SELECT P.* FROM
  ( SELECT COL1...COLN, ROW_NUMBER() OVER ID_COLUMN AS RN FROM MY_TABLE )
WHERE P.RN BETWEEN START_INDEX AND END_INDEX;

Instead of an explicit limit function, this alternate means needs to be used. (If there is a better way, please let me know)

One of us argued that this means that Oracle actually fetches END_INDEX records and then only returns those records which have a rn over START_INDEX. This means that when looking for records 123,432-123,442 Oracle would retrieve 123,431 unnecessary records. It was then argued that it followed that the two open source DB's mentioned (MySQL & PgSQL) by implication have a means of shortcutting this.

The counter argument is that DBMS's are optimized to handle sub-queries, meaning that the syntax does not necessarily imply the behavior. Further, the LIMIT syntaxes are likely merely syntactic sugar which are re开发者_StackOverflow中文版ally wrappers around what has to be stated explicitly in Oracle.

Is there any who can determine which of these is the correct interpretation? Perhaps both are correct in some way?

---

It is correct that Oracle processes END_INDEX rows and discards rows 1 to START_INDEX-1, but it only fetches rows START_INDEX to END_INDEX in the cursor.

I don't know how LIMIT is implemented in the other DBMSs, but I can't imagine how they could do otherwise: how would they know they are fetching the 123,432nd row of the result set without first finding and discarding the previous 123,431 rows?

In practice, if you find yourself applying a LIMIT clause (or Oracle equivalent) with a START_INDEX of more than a few hundreds, you really need to rethink your requirements.