Paging in ORACLE 10g

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Senior Member
Posts: 65
Joined: Mon Oct 03, 2005 6:10 am

Paging in ORACLE 10g

Postby naveedplayer » Thu Aug 16, 2007 9:07 am

Hi Tim,
How are you?...Tim! I have a fairly simple query which returns huge amount of records (around 75000). I want to implement paging in its result set. That I may want to fetch records from ROWNUM between 500 and 1000, and consequently 1001 - 1500 etc. This means I don't need to fetch all the records. I just need chunks of data based on Page Number and Page Size. What I have currently done about it is:

Code: Select all

SELECT * from
SELECT id, name, ROWNUM as rownumber from Customer
where rownumber between 500 and 1000

In above example 500 and 1000 are predetermined variable values.

As you can see, every time I need to fetch the next chunk, I have to fetch all the records first and then have to filter out the required resultset. I want to achieve this in a single query like we use LIMIT and OFFSET in mysql etc. How would you implement paging in this case. I am using ORACLE 10g

Waiting for your reply.

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Paging in ORACLE 10g

Postby Tim... » Thu Aug 16, 2007 10:04 am


The standard way to do this is to use a Top-N query, similar to what you've used:

Code: Select all

select *
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= MAX_ROWS )
 where rnum >= MIN_ROWS

Oracle has optimized the top-n queries, so they are not as resource intensive as they see at first glance.

See: ... 7412348064 ... 6812348057

Chances are, mySQL is doing similar work to get the results, after all, it has to run the query to know the order of the data before t can decide which rows represent the upper and lower bounds. It's just the syntax used in mySQL makes it look cleaner and simpler.


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests