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.

5 comments

@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.

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.

Performance

As @wweicker points out in the comments below, performance is an issue with the new syntax in 12c. I didn't have a copy of 18c to test if Oracle has since improved it.

Interestingly enough, my actual results were returned slightly quicker the first time I ran the queries on my table (113 million+ rows) for the new method:

  • New method: 0.013 seconds.
  • Old method: 0.107 seconds.

However, as @wweicker mentioned, the explain plan looks much worse for the new method:

  • New method cost: 300,110
  • Old method cost: 30

The new syntax caused a full scan of the index on my column, which was the entire cost. Chances are, things get much worse when limiting on unindexed data.

Let's have a look when including a single unindexed column on the previous dataset:

  • New method time/cost: 189.55 seconds/998,908
  • Old method time/cost: 1.973 seconds/256

Summary: use with caution until Oracle improves this handling. If you have an index to work with, perhaps you can get away with using the new method.

Hopefully I'll have a copy of 18c to play with soon and can update

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

This is great answer for 12c users

@wweicker 2019-03-22 15:43:23

Syntax is cleaner, but performance is worse (dba-presents.com/index.php/databases/oracle/…)

@JoelC 2019-03-25 13:26:39

Good to know, thanks @wweicker. Hopefully the performance gets fixed by Oracle soon; although, knowing Oracle, this might be a distant hope!

@Lukasz Szozda 2019-08-18 19:10:27

Syntax is new and it is transformed to regular ROW_NUMBER/RANK calls. Related How do I limit the number of rows returned by an Oracle query after ordering?

@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

SELECT * FROM V$VERSION

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

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]

@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) */
     object_id,object_name,
     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 * 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)

@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 - inf.unideb.hu/~gabora/pagination/results.html

@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: orafaq.com/wiki/ROWNUM

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

pagination n!/(n-r)!

@Aetherus 2019-07-01 08:02:53

Oracle pagination ruins my day.

Related Questions

Sponsored Content

16 Answered Questions

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

20 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2085753 View
  • 1053 Score
  • 20 Answer
  • Tags:   sql oracle

1 Answered Questions

[SOLVED] Call Oracle stored procedure with no arguments

5 Answered Questions

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

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
  • 2653 View
  • 5 Score
  • 3 Answer
  • Tags:   sql oracle

Sponsored Content