按欄位排序的Oracle分頁不是唯一的性能

通常，如果 order by field 是唯一的，我們會這樣編寫分頁 SQL：

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;

但在我的情況下，該L_DATE欄位不是唯一的，甚至可能也是空的。我不想在後面放更多的欄位L_DATE（其實這個欄位是動態來的），所以我把順序放在RN後面，像這樣：

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;

這樣無論“order by”欄位是否唯一，頁面數據都是正確的，但性能比第一個慢3倍。

有什麼建議？

---

現在，我的解決方案是：

select *
from ( select xx.*, rownum as rn
      from (select * from  T_LOG ) xx ) xxx
where rn >0 and  rn <=500;

這樣，無論按欄位排序是否唯一，數據總是以正確的方式出現，甚至不按欄位排序。

以下方式似乎是錯誤的：

select *
from ( select xx.*, rownum as rn
      from (select * from  T_LOG ) xx
      where rownum <= 500 ) xxx
where rn >0;

在某些頁面之後它會獲得重複的數據，如果語句按唯一欄位排序，則它可以正常工作。

這也是錯誤的，因為現在你得到 500 行（可能是任何 500 行）然後對它們進行排序。如果L_DATE不足以給出確定性答案，您確實需要添加更多列。最明顯的選擇是主鍵列：

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;

…因此，在L_DATE（或使用者指定的任何列）中沒有好的資訊的情況下，您將根據確定性進行排序。

另外：如果你這樣做了，select ... from table where rownum <= 10那麼你得到的 10 行隨著時間的推移是不確定的。行沒有預定義的順序。

引用自：https://dba.stackexchange.com/questions/196689