Oracle rownum < [n] alternatives? Understanding Of?

Question

From my understanding, rownum is applied to the entire result set after it has been queried. This means if I wanted to limit results using rownum, it would still query everything at first. I have a user table that has over a hundred thousand records. I also am developing a site that searches this table bringing back a result set. Unfortunately, the requester wants me to include the ability to search on JUST the last name.

Imagine the about of "jones", "whites", "browns" that could come back. I would like to bring back no more than 200 records, is there a better way to do this instead of using rownum? Is my understanding on when rownum is applied correct?

Answer 1

SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        WHERE   lastname = 'Jones'
        ORDER BY
                id
        )
WHERE   rownum <= 200

or

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) rn
        FROM    mytable
        WHERE   lastname = 'Jones'
        )
WHERE   rn <= 200

The latter was slower in 9i but works just the same in 10g+.

From my understanding, rownum is applied to the entire result set after it has been queried

No. The rownum is applied as soon as each record satisfying the WHERE clause is fetched (but before they are ordered).

Actually, the nested query is required here because ROWNUM is evaluated before the ORDER BY.

Both ROWNUM and ROW_NUMBER() are subject to optimization. If you have an index on (lastname, id), the query will use the index and stop after returning the 200th record (you will see a COUNT(STOPKEY) in the plan).

Also there is a common caveat with ROWNUM and paging. This query:

SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        WHERE   lastname = 'Jones'
        ORDER BY
                id
        )
WHERE   rownum BETWEEN 201 AND 400

will never return anything, because ROWNUM is itself a part of the WHERE condition. The engine just cannot return the first row because it would have ROWNUM = 1 which does not satisfy the WHERE clause.

To work around this, you would have to double-nest the query:

SELECT  *
FROM    (
        SELECT  q.*, ROWNUM AS rn
        FROM    (
                SELECT  *
                FROM    mytable
                WHERE   lastname = 'Jones'
                ORDER BY
                        id
                ) q
        )
WHERE   rn BETWEEN 201 AND 400

This will be optimized to a COUNT(STOPKEY) too.