Paging a Query Resultset

hi,

in a .NET driven project we got the demand to sample some ideas for paging of resultsets. With a database driven Client fetching of a complete resultset could last a very long time. First all the rows have to be fetched accross the network to the client. Then the client has to store the complete resultset in memory and probably has to convert the 'raw' rows into another format of data for example objects.

Resources are not unlimited - as we know ;-) - so we should find an approach of - i like to call it - an 'endless machine';

With google and searching for 'range paging' i found an interesteting article how to page resultsets in an very effektive way. The core functionality is very simple:

SELECT * FROM (
SELECT /*+ FIRST_ROWS */ IQ.*, ROWNUM AS Z_R_N FROM (
SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC
) IQ WHERE ROWNUM < :P0)
WHERE Z_R_N >= :P1

The blue business query is ceated by the client and wrapped by two nested range queries. This construct is he way to do this with an Oracle database. Setting the positional parameters :P0/:P1 declares the window in the result set. When :P0 is 40 and :P1 is 20 then all rows in the window from r0w 21 till row 40 are ready to be fetched (the resultset should be larger the 40 ;-)). The FIRST_ROWS hint is a tip for the Oracle optimizer to use the most effective path to retrieve the data thru the range query. The design of the query is from Tom Kyte.

But the core construct does not handle the logic around paging. There must be some control around the construct - this resulted in the utl_page PL/SQL package.


The Package design is simple.

A method (Create_Area) creates and tests the parameters for the query window

• the business query like : SELECT * FROM MY_OBJECTS ORDER BY OBJECT_NAME
• the pages size for example 20
  

A method (Get_Nextpage)

• which returns a REF Cursor (this is a variable which contains all information about an opened resultset originated by a given query) - Or simple returns the next page of a result set.
  

A method (Get_Prevpage)

• which returns a REF Curser - The previous page of the resultset.
  

Ok let's begin. I used the Visuals Studio .NET 2003 with the Oracle plugin Oracle Developer Tools for Visual Studio .NET. First let us create the Paging Area :







Now fetch the next page :
The query is ordered by object_name which you can see. An additional Colum Z_R_N tells you the position of the row int the complete rowset. Position 1 to 20 is fetched.






Fetch the next Page - row 21 to 40 :




Last but not least fetch of the previus page - rows 1 to 20







That's it. Sure - this is not the only one possible way to solve the demand. You could store the unique id's of a resultset in a temporary table to use this as baseline for paging. But if i can achieve it i do not write to the database for some paging.

If you want to get the PL/SQL Source Code then write to my employee-email kreitsch@csc.com and i will send it to you.



Another step could be to impelement the logic with a C# Class. I know that a lot of OO-oriented developers do not like to code with the PL/SQL :-(.
In a future article i like to write about the future of .NET and Oracle - a great future!
Bye
Carl