Skip to content

Oracle limit and offset issue when query is ordered #99

Closed
berislavbabic opened this Issue Feb 17, 2012 · 3 comments

5 participants

@berislavbabic

A weird issue happens when your query is ordered, and then paginated to include limit and offset. It returns last couple of rows(this is weird, because there is no strict number the database will return) for all pages, and after few pages it just freezes the query and returns the same rows for any offset and limit.
This is all tested on oracle 11gR2x64, I also ran the query in SQLDeveloper and got the same results. So it's a database problem. I found a workaround that works, and i will make a pull request that solves this issue, at least for me, it should probably be tested by someone else.

@tenderlove
Ruby on Rails member

Merged your change, so I'm closing this. Thanks!

@t0m
t0m commented Jan 8, 2013

I ran into this too and the reason the results "freeze" like that is explained a bit in this ask tom article under the "Pagination with ROWNUM" section. In a nutshell, oracle requires that your order by clause contains a unique column or the results will be non-deterministic.

The submitted fix will work but it comes with a couple performance penalties:

  • Sorting on indexed columns will always result in a full table scan instead of using the index.
  • The COUNT STOPKEY optimization will never be used.

I've found that changing the paging query from:

WHERE raw_rnum_ between #{offset.expr.to_i + 1 } and #{offset.expr.to_i + limit}

to:

WHERE raw_rnum_ >= #{offset.expr.to_i + 1 } and rownum <= #{limit}

gives the same results but with both of the optimizations added back in. I've made the changes in this branch and the results are noticeably quicker on my app. Could anyone else give a quick double check to make sure they see the same performance gains?

@eduardordm eduardordm referenced this issue in rsim/oracle-enhanced Mar 15, 2013
Closed

Performance issues when using offset #284

@eduardordm

Hey @t0m, the upper bound needs to be limited within the query, not outside of it. The @babinho commit is completely broken and it should be reverted, the original issue is way less damaging than the current one (and I couldn't reproduce actually). I think this issue didn't get visibility because most oracle users are larger companies that are still on older versions like mine.

@tenderlove please revert this commit, save us.

@rafaelfranca rafaelfranca pushed a commit that referenced this issue Mar 15, 2013
@eduardordm eduardordm Revert issue #99 fixes
There are two commits involving issue #99 which should be reverted.

First of all, #99 is not an issue in Arel at all. Second, the fix provides pretty much destroys the purpose of pagination by cause full table scans.

The original code (it seems I can't simply revert the commits) is 900 times slower than this one.

```
SELECT * FROM (
   SELECT raw_sql_.*, rownum raw_rnum_
   FROM (SELECT "LANCAMENTOS".* FROM "LANCAMENTOS" ) raw_sql_
 )
 WHERE raw_rnum_ between 1 and 30

----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |  4636K|  2701M| 23442   (2)| 00:04:42 |
|*  1 |  VIEW               |             |  4636K|  2701M| 23442   (2)| 00:04:42 |
|   2 |   COUNT             |             |       |       |            |          |
|   3 |    TABLE ACCESS FULL| LANCAMENTOS |  4636K|   738M| 23442   (2)| 00:04:42 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RAW_RNUM_"<=30 AND "RAW_RNUM_">=1)

   Statistics
-----------------------------------------------------------
               4  user calls
              13  physical read total multi block requests
       202588160  physical read total bytes
       202588160  cell physical IO interconnect bytes
               0  commit cleanout failures: block lost
               0  IMU commits
               0  IMU Flushes
               0  IMU contention
               0  IMU bind flushes
               0  IMU mbu flush

SELECT * FROM (
   SELECT raw_sql_.*, rownum raw_rnum_
   FROM (SELECT "LANCAMENTOS".* FROM "LANCAMENTOS" ) raw_sql_
   WHERE rownum <= 30
 )
 WHERE raw_rnum_ >= 0

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    30 | 18330 |     2   (0)| 00:00:01 |
|*  1 |  VIEW               |             |    30 | 18330 |     2   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |             |       |       |            |          |
|   3 |    TABLE ACCESS FULL| LANCAMENTOS |    30 |  5010 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RAW_RNUM_">=0)
   2 - filter(ROWNUM<=30)

   Statistics
-----------------------------------------------------------
               4  user calls
               0  physical read total multi block requests
               0  physical read total bytes
               0  cell physical IO interconnect bytes
               0  commit cleanout failures: block lost
               0  IMU commits
               0  IMU Flushes
               0  IMU contention
               0  IMU bind flushes
               0  IMU mbu flush
```
5399048
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.