RIFE : Blogs : Limit and offset in Oracle

Blogs : Archives

Shameless plug - I need your help

Please vote for my band Flytecase at http://vote.flytecase.be.

Your support can provide us with a record deal and numerous high profile concerts!

For quite a while I've been frustrated with Oracle's lack of doing limit and offset in an easy single query. This always made it tedious to display paged results from a database query in a web page. Today, after looking on the web for the xth time, I stumbled into a query that seems to solve this.

So without further ado, here it is for those that have been searching for it also:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY column1) LINENUM, column1, column2
    FROM MyTable
    ORDER BY column1
)
WHERE LINENUM BETWEEN 100 AND 200;

posted by Geert Bevin in Computing on Jan 25, 2004 12:42 PM : 11 comments [permalink]

Comments

Re: Limit and offset in Oracle

Hi,

This is a very useful query for bringing back the primary key but I find if I replace column2 with a non-indexed field the query slows ... from sub-second to 12 seconds for a table with 100k rows!

Any suggestions?
Thanks,
L

posted by Laoise on Jul 8, 2004 1:39 PM

Re: Limit and offset in Oracle

Euhm ... add the index on that column?

posted by Geert Bevin on Jul 8, 2004 2:11 PM

Re: Limit and offset in Oracle

Thanks for response. Yes that would be logical:o) But ... I'd like to be able to bring back a variety of columns and don't want to index them all because this is an audit table where inserts have to be given priority.

In DB2 the query works fine if I select the primary key and a number of other fields. If I do the same in Oracle it does a full table scan even though I'm retrieving the primary key as the first field in the query.

I tried the first_rows hint but it didn't help. Any other thoughts?

posted by Laoise on Jul 9, 2004 1:29 PM

Re: Limit and offset in Oracle

No sorry.

posted by Geert Bevin on Jul 9, 2004 2:38 PM

Re: Limit and offset in Oracle

Besides from being very slow on non-indexed columns, I don't think this query returns the correct results (at least it did for my queries, where rows 100 till 200 where different when I executed it with and without the LINENUM stuff)

A faster and cleaner way that always works, even with ordering on multiple columns, is this:

select * from (

select query.*, rownum rnum from (

select table1.column1, table1.column2, … from table1, table2, … where … order by ...

) query where rownum <= 200

) where rnum >= 100 order by rnum

posted by Wim on Mar 9, 2005 4:57 PM

Re: Limit and offset in Oracle

Wim is wrong, it does return the correct results. However one tiny niggle is that "ORDER BY column1" on the inner query should be brought to the outside query as "ORDER BY LINENUM" since the outer query is not guaranteed to return the rows of the inner query in the order you might expect (the order of the inner query) without that. That could be why Wim didn't see the rows matching up (because the order was muddled).

posted by Soulcutter on Aug 20, 2006 4:21 AM

Re: Limit and offset in Oracle

This one is great it works for me!!!!!!

Thanks a lot !!!!!

posted by Anonymous on Mar 30, 2007 10:47 PM

Re: Limit and offset in Oracle

I think this is very simple and working, I don't have time to test it right now but the little test i have done makes me believe this is cool and simple solution

select * from table where rownum between 11 and 20;

posted by warrior on Oct 31, 2007 12:42 PM

Re: Limit and offset in Oracle

i'm not get it 'cutter ... can u give me just the query other than that explanation ...
how about this ... i found that this query is works for me ... it's my modify from Geert's query

select * from (select row_number() over (order by column1 desc/asc ) LINENUM, columnX,column Y [whatever column u want to ...] from table_name )
where linenum between10 and 20;

i got at least 1-2 secs for 87950 rows selected using toad as my editor.

posted by sync on Jan 11, 2008 4:15 AM

Re: Limit and offset in Oracle

this works for me:

select * from (select * from countries order by country_id desc) where rownum between 1 and 40

posted by Pille on Sep 27, 2008 2:57 AM

Re: Limit and offset in Oracle

i must correct me, this just works

entries 10-20:

select * from (select * from countries order by country_id desc) where rownum <= 20
minus
select * from (select * from countries order by country_id desc) where rownum <= 10

Navigation

Show blog menu

Hide blog menu

Authentication

Search in blogs

Blog Users

Recent Entries

General

Use it

Documentation

Support

Sibling projects

Blogs : Archives

Comments

Add a new comment