Oracle pagination with order by field not unique performance

Question

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 <= 500
) XXX
WHERE RN > 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 <= 500
) XXX
WHERE RN > 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 >0 and  rn <=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 <= 500 ) xxx
where rn >0;

It's getting repeated data after some pages, if statements had order by unique field, it's work correctly.

Answer 1

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 <= 500
) XXX
WHERE RN > 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 <= 10 then the 10 rows you get back is not deterministic over time. There is no predefined order to the rows.