Oracle11g select query with pagination

Question

I am facing a big performance problem when trying to get a list of objects with pagination from an oracle11g database.

As far as I know and as much as I have checked online, the only way to achieve pagination in oracle11g is the following : Example : [page=1, size=100]

SELECT * FROM
  (
    SELECT pagination.*, rownum r__  FROM
      (
         select * from "TABLE_NAME" t
         inner join X on X.id = t.id
         inner join .....
         where ......
         order
      ) pagination
    WHERE rownum <= 200 
  )
WHERE r__ > 100

The problem in this query, is that the most inner query fetching data from the table "TABLE_NAME" is returning a huge amount of data and causing the overall query to take 8 seconds (there are around 2 Million records returned after applying the where clause, and it contains 9 or 10 join clause).

The reason of this is that the most inner query is fetching all the data that respects the where clause and then the second query is getting the 200 rows, and the third to exclude the first 100 to get the second pages' data we need.

Isn't there a way to do that in one query, in a way to fetch the second pages' data that we need without having to do all these steps and cause performance issues?

Thank you!!

does this answer your question: https://stackoverflow.com/questions/241622/paging-with-oracle — Roberto Hernandez, Sep 02 '21 at 14:43

score 0 · Answer 1 · answered Sep 02 '21 at 14:53

It depends on your sorting options (order by ...): database needs to sort whole dataset before applying outer where rownum<200 because of your order by clause.

It will fetch only 200 rows if you remove your order by clause. In some cases oracle can avoid sort operations (for example, if oracle can use some indexes to get requested data in the required order). Btw, Oracle uses optimized sorting operations in case of rownum<N predicates: it doesn't sort full dataset, it just gets top N records instead.

You can investigate sort operations deeper using sort trace event: alter session set events '10032 trace name context forever, level 10';

Furthermore, sometimes it's better to use analytic functions like

select *
from (
   select
      t1.*
     ,t2.*
     ,row_number()over([partition by ...] order by ...) rn
   from t1
       ,t2
   where ...
   )
where rn <=200
and rn>=100

because in some specific cases Oracle can transform your query to push sorting and sort filter predicates to the earliest possible steps.

Oracle11g select query with pagination

1 Answers1