Home > Grimoire, Oracle > Oracle: Paging Results

Oracle: Paging Results

Paging Oracle developers…

This will be a quick post, as the problem is small.  I will add, though, that setting up paging with database queries can be a major pain in the ass.  Setting up the consumer – usually jQuery, ExtJS, DWR, or some other AJAX client, is hard enough, but then you have to translate the client’s version of “start-limit” to the persistance-side’s language.  Sometimes it’s a simple start/limit pair, sometimes it’s a start/page pair, and then there’s Oracle, which decided to punt on the whole thing.

The right way

Let’s take a look at a database that actually likes developers.  I’ve been using MySQL since version 2 (yes, I am very old), and paging is about easy to understand and implement as it could be.  Let’s look at a sample select statement:

SELECT id,first_name,last_name
FROM users
WHERE first_name LIKE '%?%'
LIMIT $offset,$pgsize
ORDER BY id

The above is pretty self-explanatory, but since I revel in the minutiae, I will expound upon the finer points anyway. Paging in MySQL is implemented with the LIMIT clause. The offset is the starting record to return, and the page size is the number of records after the offset record to return.  For example, if the clause read “LIMIT 20,10”, you will be returned record number 20 through 29.  It’s almost like MySQL was developed by people who actually have to use it.

The “Screw Developers!” way

Oracle has no LIMIT clause.  In fact, Oracle has no direct way at all to limit the number of records returned from a result set.  What most Oracle admins will tell you is that paging is what ROWNUM is for – limiting the number of records returned.  What they won’t tell you because they don’t want to believe it themselves or they just don’t know is that ROWNUM was originally for internal optimization only, and was only exposed once developers bugged Oracle enough.  ROWNUM is like using a screwdriver to dig a hole.  Even though it’s not a shovel, it will eventually do the job – it will just take a lot more work. We will get back to ROWNUM in sec, but for now, bask in the glory of the ROW_NUMBER analytic function:

ROW_NUMBER(<value>) OVER (<partition_clause> ORDER BY <order_by_clause>)
Assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order by clause, beginning with 1.

And the code:

SELECT * FROM (
SELECT id,first_name,last_name ROW_NUMBER() OVER (ORDER BY id) AS rownumber
FROM users
WHERE first_name LIKE '%?%'
)
WHERE rownumber BETWEEN :offset AND :offset+:pgsize

This is how you do paging in Oracle. Ignore the fact that the placeholders after BETWEEN are not standard and just spend a moment taking in all the backflips you have to do to get paged results out of a database that can cost your company millions of dollars in license fees, and compare it to the previous example that works with a free database that by most accounts runs select statements almost twice as fast.

Not only are you introducing a sub-select which I’m sure does nothing for your performance, but you are also forced to use the ROW_NUMBER() OVER function just to get the numbering right. The reason for having to use ROW_NUMBER() is actually an indicator that ROWNUM was not introduced for the reasons Oracle will give you. It is also an indicator that Oracle likes bilking your company for consulting fees.

A pitfall (READ: Don’t do this.)

Take this statement:

SELECT * FROM (
SELECT id,first_name,last_name,ROWNUM AS rownumber
FROM users
WHERE first_name LIKE '%?%'
ORDER BY id
)
WHERE rownumber BETWEEN :offset AND :offset+:pgsize

You would think this works. In fact, this is what you probably would figure out to try first – until you added the ORDER BY clause. Your Oracle admin would give you this structure and tell you everything will be fine. You have to keep in mind, though, that Oracle (the company) hates you and your admin, and doesn’t mind that its gross negligence would cause you to miss your agile-imposed micro-deadline and screw up the sprint’s gantt chart resulting in an attempt to surreptitiously change your entry in the project RACI chart from ‘A’ to ‘R’. Because it doesn’t work. The problem is that Oracle runs the ORDER BY clause after the results are selected, and ROWNUM is populated during that selection process. The result you get will usually look out of order because of this, leaving you with pages of results that seem to indicate that the ORDER BY clause is being ignored, even though it is executing just fine.

Look at the upside – Oracle owns MySQL now. Now if you’ll excuse me, I’m going to start reacquainting myself with postgre…

Categories: Grimoire, Oracle Tags: , , , ,
  1. No comments yet.
  1. No trackbacks yet.

*