Paging with Oracle

问题:

I am not as familiar with Oracle as I would like to be. I have some 250k records, and I want to display them 100 per page. Currently I have one stored procedure which retrieves all quarter of a million records to a dataset using a data adapter, and dataset, and the dataadapter.Fill(dataset) method on the results from the stored proc. If I have \"Page Number\" and \"Number of records per page\" as integer values I can pass as parameters, what would be the best way to get back just that particular section. Say, if I pass 10 as a page number, and 120 as number of pages, from the select statement it would give me the 1880th through 1200th, or something like that, my math in my head might be off.

I\'m doing this in .NET with C#, thought that\'s not important, if I can get it right on the sql side, then I should be cool.

Update: I was able to use Brian\'s suggestion, and it is working great. I\'d like to work on some optimization, but the pages are coming up in 4 to 5 seconds rather than a minute, and my paging control was able to integrate in very well with my new stored procs.

回答1:

Something like this should work: From Frans Bouma\'s Blog

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)

回答2:

Ask Tom on pagination and very, very useful analytic functions.

This is excerpt from that 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;

回答3:

In the interest of completeness, for people looking for a more modern solution, in Oracle 12c there are some new features including better paging and top handling.

Paging

The paging looks like this:

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

Top N Records

Getting the top records looks like this:

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

Notice how both the above query examples have ORDER BY clauses. The new commands respect these and are run on the sorted data.

I couldn\'t find a good Oracle reference page for FETCH or OFFSET but this page has a great overview of these new features.

回答4:

Just want to summarize the answers and comments. There are a number of ways doing a pagination.

Prior to oracle 12c there were no OFFSET/FETCH functionality, so take a look at whitepaper as the @jasonk suggested. It\'s the most complete article I found about different methods with detailed explanation of advantages and disadvantages. It would take a significant amount of time to copy-paste them here, so I won\'t do it.

There is also a good article from jooq creators explaining some common caveats with oracle and other databases pagination. jooq\'s blogpost

Good news, since oracle 12c we have a new OFFSET/FETCH functionality. OracleMagazine 12c new features. Please refer to \"Top-N Queries and Pagination\"

You may check your oracle version by issuing the following statement

SELECT * FROM V$VERSION

回答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;

via [tecnicume]