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?
S, what we should we learn from this?
- The ROWNUM trick for 'windowing' result sets really *does* make things much faster (up to 400 times). This supports my original contention: *Never* return more data then you plan on using, ever.
- When the query is simple, make sure you have an index that matches the ORDER BY.
- I didn't show it, but I tried a solution using the ROW_NUMBER analytical function. It did not perform as well as the ROWNUM virtual field did.
- Table-scanning a single table should not be much of an issue, if your actual query in question is as simple as your example.
However, all this still does not help your original question. If your base query is complex, but the results are ordered, there is precious little that can be done. In the realm of what is possible, however, the ROWNUM trick is absolutely your best bet.
Hope this helps,
NOTE: All tests were run multiple times. I am on an isolated server - it is all mine. Nothing else is running on it. The cache is large enough to hold the entire target table and indexes, so no physical reads were necessary for any of the tests.