萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

解決 Oracle 分頁查詢排序與效能問題

原始查詢與資料樣本

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;

關鍵點:

  1. 最內層先 ORDER BY
  2. 中間層加 ROWNUM <= max_row 限制最大筆數(CBO 可下推提前終止)
  3. 最外層過濾 rn > min_row

效能優化關鍵

WHERE ROWNUM <= 40 可被 CBO 下推至內層,查詢滿 40 筆即停止;若改用 BETWEEN 21 AND 40 在最外層,CBO 無法下推,需全量掃描再過濾,效率大幅下降。建議加 /*+ FIRST_ROWS */ 引導 CBO 選擇 NESTED LOOPS 而非 HASH JOIN。

copyright © 萬盛學電腦網 all rights reserved