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?
03-17-2001, 02:42 PM
I think you should use PL/SQL Table for this. Collect whole record set from physical table to PL/SQL table with lastname, firstname and start index 1 incremented by 1.
From this index value you can easily separate 25 records to display. In this case only once query is fired against database. Next time u have result set in memory which u can display easily.
03-19-2001, 04:21 PM
Can you please give an example hoe you Mean... If possible give me an Code That could help... Thanks..
03-21-2001, 01:05 PM
Sorry Kancha, but that is absolutely *not* what you want to do. *Never* bring back more data than you want - to PL/SQL or any other routine.
The issue at hand is the performance of the query given that we only wish to display 25 rows.
Pulling *all* 10,000 rows across the wire and populating a PL/SQL table *before* restricting the list is certainly *not* going to help.
As far as the performance of the original query goes...
Unfortunately, you can't do much better.
On the bright side, however, the performance should not be too bad.
Here are some examples for you. I performed the same types of queries against a 22,000 record table...
When the ORDER BY field is NOT indexed
AND the ROWNUM restriction IS used
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.