Paging with Oracle

Question

I am not so familiar with Oracle as I would like to be. I have 250 thousand records, and I want to display them at 100 per page. Currently, I have one stored procedure that extracts all a quarter of a million records into a data set using a data adapter and data set, as well as the dataadapter.Fill method (data set) from the results of the stored procedure. If I have "Page Number" and "Number of records per page" as integer values ​​that I can pass as parameters, that would be the best way to return this particular section. Say, if I pass 10 as the page number, and 120 as the number of pages, from the select clause this will give me the 1880s to the 1200th, or something like that, my math in my head might be turned off.

I do this in .NET with C #, I thought that it doesn’t matter, if I can do it correctly on the SQL side, then I should be fine.

Update: I was able to use Brian's suggestion and it works great. I would like to work on some optimization, but the pages appear within 4-5 seconds, and not in a minute, and my paging control was able to integrate very well with my new stored procedures.

Answer 1

Something like this should work: From Frans Bouma Magazine

SELECT * FROM ( SELECT a.*, rownum r__ FROM ( SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%' ORDER BY OrderDate DESC, ShippingDate DESC ) a WHERE rownum < ((pageNumber * pageSize) + 1 ) ) WHERE r__ >= (((pageNumber-1) * pageSize) + 1) 

Answer 2

Ask Tom about page separation and very, very useful analytic functions.

This is an excerpt from this page:

 select * from ( select /*+ first_rows(25) */ object_id,object_name, row_number() over (order by object_id) rn from all_objects) where rn between :n and :m order by rn; 

Answer 3

For the sake of completeness, for people who are looking for a more modern solution, Oracle 12c has several new features, including improved swap and top processing.

Paging

Paging is as follows:

 SELECT * FROM user ORDER BY first_name OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY; 

Top N Posts

Getting the top entries is as follows:

 SELECT * FROM user ORDER BY first_name FETCH FIRST 5 ROWS ONLY 

Note that both of the above query examples have ORDER BY clauses. New teams respect them and run on sorted data.

I could not find a good Oracle reference page for FETCH or OFFSET , but this page has a great overview of these new features.

Answer 4

I just want to summarize the answers and comments. There are several ways to paginate.

Prior to oracle 12c, there was no OFFSET / FETCH functionality, so look at the white paper as @jasonk suggested. This is the most comprehensive article I have found about various methods with a detailed explanation of the advantages and disadvantages. It will take a long time to copy them here, so I will not do this.

There is also a good article from the creators of jooq explaining some common oracle and caveat reservations. jooq blogpost

Good news, since oracle 12c has new OFFSET / FETCH functionality. New features in OracleMagazine 12c . See "Most Popular Queries and Pagination"

You can check the oracle version by issuing the following statement

 SELECT * FROM V$VERSION 

Answer 5

Try the following:

 SELECT * FROM (SELECT FIELDA, FIELDB, FIELDC, ROW_NUMBER() OVER (ORDER BY FIELDC) R FROM TABLE_NAME WHERE FIELDA = 10 ) WHERE R >= 10 AND R <= 15; 

through [tecnicume]