By stephenbayer

2008-10-27 22:37:21 8 Comments

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.


@Vadim Kirilchuk 2015-11-03 11:32:28

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


@JoelC 2015-04-28 19:04:42

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.


The paging looks like this:

FROM user
ORDER BY first_name

Top N Records

Getting the top records looks like this:

FROM user
ORDER BY first_name

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.

@Lalji Gajera 2019-02-14 10:40:40

This is great answer for 12c users

@Furetto 2013-12-16 17:33:01

Try the following:

WHERE R >= 10
AND R   <= 15;

via [tecnicume]

@Chobicus 2008-10-27 22:57:20

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

This is excerpt from that page:

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

@tallseth 2012-04-03 16:46:50

This is actually a much better implementation, though it is hard to find on that post. When you have a lot of large pages, the other answer must go over all rows from prior pages as well. In complicated queries, this means that later pages perform worse than earlier pages.

@Chobicus 2012-04-05 08:18:35

@tallseth You're right. It's hard to find it on that page. Excerpt is added.

@chakeda 2018-08-30 20:16:05

This is the correct answer should you want to dynamically change your order.

@Brian Schmitt 2008-10-27 22:46:30

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

    SELECT a.*, rownum r__
        ORDER BY OrderDate DESC, ShippingDate DESC
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)

@James P. 2011-11-20 17:33:49

Is rownum a reserved word?

@Brian Schmitt 2011-11-20 18:54:24

Yes it's a 'built in' column that Oracle supports, it always starts at 1 and increments for each row. So in this snippet of code, if you have 1000 rows, the sort order is applied and then each row is assigned a rownum. The outer select(s) use those row numbers to locate the 'page' you are looking for based on your pagesize.

@newhouse 2012-08-06 11:58:09

This is nice, but horribly slow on large selects, just check what will be the time to select 0 to 1000 and 500.000 to 501.000... I was using this kind of select structure now I'm searching for a workaround.

@jasonk 2012-08-26 23:26:05

@n3whous3 you might try this -

@Chris Holmes 2012-08-31 22:04:42

Oracle is telling me that the "a" alias is ambiguous. Any reason why I'd get this error?

@jim 2012-11-02 17:41:54

@ChrisHolmes did you ever get an answer to your question. I get the same error

@Chris Holmes 2012-11-03 15:02:20

I did not get an answer. I had to wrestle with the query, but I got it working eventually. I cannot remember what I did. I still have the query though... Let me see if I can figure out what I did and I'll post the answer here.

@Mengdi Gao 2012-12-17 06:09:32

I wondered why two WHERE couldn't be combined with AND, and then found this:

@Premraj 2014-02-27 05:52:28

pagination n!/(n-r)!

Related Questions

Sponsored Content

14 Answered Questions

[SOLVED] How do I limit the number of rows returned by an Oracle query after ordering?

19 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2002941 View
  • 1001 Score
  • 19 Answer
  • Tags:   sql oracle

1 Answered Questions

[SOLVED] Call Oracle stored procedure with no arguments

1 Answered Questions

[SOLVED] Oracle procedure with CDT as input parameters is very slow

0 Answered Questions

Can i join on an inbound oracle cursor in a stored proc?

1 Answered Questions

[SOLVED] Compilation errors for oracle SQL - stored procedure

3 Answered Questions

2 Answered Questions

2 Answered Questions

[SOLVED] Fastest way to move records from an Oracle database into SQL Server

3 Answered Questions

[SOLVED] How can I efficiently query for contiguous sets of dates in my data set?

  • 2009-06-02 05:03:42
  • Aaron Rustad
  • 2602 View
  • 5 Score
  • 3 Answer
  • Tags:   sql oracle

Sponsored Content