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 ) a where rownum <= 19 ) where r => 10;To query the 20th through the 29th rows, just change the numbers above.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.