I have an EMPLOYEE table with 10,000 records and I only want to display 25 records at a time to my web client. I have been using ROWNUM to minimize the resultset which works fine. Unfortunately the query still needs to parse through all 10,000 records which makes it highly inefficient.
Here's an example: Lets say I want to display records 51-75 in my web client, ordered by lastname, firstname. This is what my SQL looks like.
(select firstname, lastname, rownum as newcnt
(select firstname, lastname
order by lastname, firstname)
where rownum <= 75)
where newcnt > 50
Even though I am only displaying 25 records to my client I am still parsing though 10,000 records each time due to the forced inline order by query. Is there any way I can optimize this query?