Problem solve Get help with specific problems with your technologies, process and projects.

Paging without running a query a second time?

I want to take in the number of records, starting record and ending record as parameters to a stored procedure. The stored procedure has a complex query, and the records are populated into a temp table. The first time the request is made, the query is executed and results are sent. When a request is sent for another page, the query is run another time and slows down the process. How can I implement paging without running the query second time?

If you want to query for one "page" of data for your Web site or application, and then query for another "page" of data, you will have to run the query more than once. Hopefully, your query that returns all of the rows you want to show one page at a time is pretty quick already. And if this data is queried often enough, it will already be in the buffer cache so performance will be good.

Let's assume that to get all of the rows of data, you query would be something like:

SELECT * FROM my_table WHERE col1='some_value'
ORDER BY col1;
To query the 10th to 19th records in Oracle 8i+, you can issue the following query:
select * 
from ( select a.*, rownum r 
       from ( select * from my_table
              where col1='some_value' order by col1 )
       where rownum <= 19 )
where r => 10;
To query the 20th through the 29th rows, just change the numbers above.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.