SELECT ROWNUM rn, t.id ID, o.name YYB, u.name XM, t.MC
FROM tZDYSX t, tuser u, lborganization o
WHERE t.cjr = u.id AND u.orgid = o.orgcode AND t.gx = 1
ORDER BY ID;
結果顯示 ROWNUM 與 ID 順序不一致(如 RN=3 對應 ID=49,RN=2 對應 ID=109),因 ROWNUM 產生於 ORDER BY 之前。
SELECT * FROM (
SELECT ROWNUM rn, t.id ID, o.name YYB, u.name XM, t.MC
FROM tZDYSX t, tuser u, lborganization o
WHERE t.cjr = u.id AND u.orgid = o.orgcode AND t.gx = 1
ORDER BY t.ID
) WHERE rn > 10 AND rn <= 20;
此法先產生 ROWNUM 再排序,導致分頁結果錯誤。
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT t.id ID, o.name YYB, u.name XM, t.MC
FROM tZDYSX t, tuser u, lborganization o
WHERE t.cjr = u.id AND u.orgid = o.orgcode AND t.gx = 1
ORDER BY t.ID
) a
WHERE ROWNUM <= 20
) WHERE rn > 10;
關鍵點:
ORDER BYROWNUM <= max_row 限制最大筆數(CBO 可下推提前終止)rn > min_rowWHERE ROWNUM <= 40 可被 CBO 下推至內層,查詢滿 40 筆即停止;若改用 BETWEEN 21 AND 40 在最外層,CBO 無法下推,需全量掃描再過濾,效率大幅下降。建議加 /*+ FIRST_ROWS */ 引導 CBO 選擇 NESTED LOOPS 而非 HASH JOIN。