Oracle pagination with order by field not unique performance Usually, we will write pagination SQL like this if order by field is unique:

SELECT *
FROM (
    SELECT XX.*, ROWNUM AS RN
    FROM (
        SELECT *
        FROM T_LOG
        WHERE OP_TYPE = 'Q' ORDER BY L_DATE
    ) XX
    WHERE ROWNUM &lt;= 500
) XXX
WHERE RN &gt; 0;


but in my case the L_DATE field is not unique, even maybe null too. I don't want to put more fields after L_DATE (actually, this field is dynamic coming), so I put the order by after RN, like this:

SELECT *
FROM (
    SELECT XX.*, ROWNUM AS RN
    FROM (
        SELECT *
        FROM T_LOG
        WHERE OP_TYPE = 'Q' 
    ) XX
    WHERE ROWNUM &lt;= 500
) XXX
WHERE RN &gt; 0 ORDER BY L_DATE;


In this way the page data is correct no matter whether the "order by" field is unique or not, but performance is 3 times slower than first one.

Any suggestions?



Right now, my solution is:

select *
from ( select xx.*, rownum as rn
       from (select * from  T_LOG ) xx ) xxx
where rn &gt;0 and  rn &lt;=500;


This way, data is always coming to the right way no matter order by fields is unique or not, even don't order by field.

The below way seems wrong:

select *
from ( select xx.*, rownum as rn
       from (select * from  T_LOG ) xx
       where rownum &lt;= 500 ) xxx
where rn &gt;0;


It's getting repeated data after some pages, if statements had order by unique field, it's work correctly.
It's also wrong, because now you are getting 500 rows (could be ANY 500 rows) and then sorting them.  If L_DATE is not sufficient to give a deterministic answer, you do need to add more columns.  The most obvious choice would be a primary key column:

SELECT *
FROM (
    SELECT XX.*, ROWNUM AS RN
    FROM (
        SELECT *
        FROM T_LOG
        WHERE OP_TYPE = 'Q' ORDER BY L_DATE, SOME_PK_COL
    ) XX
    WHERE ROWNUM &lt;= 500
) XXX
WHERE RN &gt; 0;


...so that in the absence of good information in L_DATE (or whatever column is nominated by the user), you will be sorting on something deterministic.

Also: If you do select ... from table where rownum &lt;= 10 then the 10 rows you get back is not deterministic over time.  There is no predefined order to the rows.