Geek asks and answers

Oracle rownum <[n] alternatives? Understanding?

In my opinion, rownum applies to the entire result set after it has been requested. This means that if I want to limit the results using rownum, it will still ask for everything at first. I have a user table that contains over one hundred thousand entries. I am also developing a site that is looking for this table by returning a result set. Unfortunately, the requester wants me to enable the search on JUST's last name.

Imagine the "john", "white", "brown" that can come back. I would like to return no more than 200 records, is there a better way to do this instead of using rownum? My understanding of when rownum is applied correctly?

+3
source share
1 answer
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 the same in 10g+.

In my opinion, rownum applies to the entire result set after it was requested

No. rownumapplied as soon as each record satisfying the sentence WHEREis retrieved (but before they are ordered).

Actually, a subquery is required here because it rownumevaluates to ORDER BY.

Both rownumand ROW_NUMBER()are optimized. If you have an index on (lastname, id), the query will use the index and stop after the 200th record is returned (in the plan you will see COUNT(STOPKEY)).

rownum . :

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

, rownum WHERE. , ROWNUM = 1, WHERE.

, :

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

COUNT(STOPKEY).

+8

More articles:


All Articles