| 查詢條件 | 結果 | 原因 |
|---|
WHERE rownum = 1 | ✅ 回傳第一列 | 首列產生 ROWNUM=1，符合條件 |
WHERE rownum <= 10 | ✅ 回傳前 10 列 | 依次產生 1~10，皆符合 |
WHERE rownum = 5 | ❌ 空結果集 | 首列 ROWNUM=1 ≠ 5，被過濾，無後續列產生 |
WHERE rownum > 5 | ❌ 空結果集 | 首列 ROWNUM=1 ≯ 5，被過濾，遞增鏈斷裂 |
WHERE rownum > 0 | ✅ 回傳所有列 | 所有 ROWNUM ≥ 1 皆符合 |
三、 分頁查詢正確寫法
錯誤嘗試
-- 第 11~20 列
SELECT * FROM t WHERE rownum BETWEEN 11 AND 20; -- 永遠空結果
標準解法：內層賦予 ROWNUM 別名，外層篩選
SELECT * FROM (
SELECT t.*, ROWNUM rn
FROM (SELECT * FROM large_table WHERE status='A' ORDER BY created_dt DESC) t
WHERE ROWNUM <= 20
)
WHERE rn >= 11;
關鍵點：
- 最內層完成業務過濾 + 排序
- 中層賦予
ROWNUM rn 並截取上限 (WHERE ROWNUM <= :end)
- 最外層篩選下限 (
WHERE rn >= :start)
12c+ 簡潔語法 (OFFSET/FETCH)
SELECT * FROM large_table
WHERE status = 'A'
ORDER BY created_dt DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
舊版替代方案：MINUS 集合運算 (效能較差)
SELECT * FROM t WHERE status='A' AND rownum <= 20
MINUS
SELECT * FROM t WHERE status='A' AND rownum <= 10;
四、 ROWNUM 停止掃描特性 (短路優化)
SELECT * FROM huge_table WHERE rownum <= 100;
Oracle **不會** Full Scan 整張表。一旦收集滿 100 列，**立即停止掃描** 回傳結果。此特性可用於：
- 大表隨機抽樣：
WHERE rownum <= 1000
- 存在性檢查：
SELECT 1 FROM t WHERE cond AND rownum = 1
- Top-N 查詢配合排序 (需內層排序)
五、 ROWID：物理地址唯一標識
結構組成 (10 位元組擴展 ROWID)
| 元件 | 位數 | 說明 |
|---|
| Data Object Number | 32 bits | 物件編號 (非 OBJECT_ID) |
| Relative File Number | 10 bits | 表空間內相對檔案號 |
| Block Number | 22 bits | 檔案內區塊號 |
| Row Slot | 16 bits | 區塊內列槽位 |
核心特性
- 唯一且固定：列插入時分配，**唯有表移動 (MOVE/匯出入/縮表) 才改變**
- 最快定位路徑：
SELECT * FROM t WHERE rowid = 'AAAP...' 直接定位區塊/槽位，**零索引、零掃描**
- 不可攜帶：跨資料庫/匯出入後 ROWID 失效
實務應用場景
| 場景 | 用法 | 注意 |
|---|
| 重複資料刪除 | DELETE FROM t WHERE rowid NOT IN (SELECT MIN(rowid) FROM t GROUP BY pk_cols); | 保留最早插入列 |
| 大表分批處理 | PL/SQL 迴圈按 ROWID 範圍分批 UPDATE/DELETE | 避免長交易/Undo 爆滿 |
| 應用層快速重訪 | 前端傳回 ROWID，後續直接定位 | 同一交易/短時間內有效 |
| 索引組織表 (IOT) 定位 | IOT 亦有 LOGICAL ROWID (含主鍵 + 猜測物理位置) | 猜測失效回退主鍵查詢 |
六、 綜合對照表
| 特性 | ROWNUM | ROWID |
|---|
| 類型 | 邏輯偽欄位 (結果集序號) | 物理偽欄位 (儲存地址) |
| 生命週期 | 查詢期間動態產生 | 列插入時固定，移動才變 |
| 唯一性 | 單一查詢內唯一 | 全資料庫唯一 (含物件號) |
| 可否 WHERE 直接用 | 受限 (=1, <=n) | 完全支援 (=, IN, BETWEEN) |
| 典型用途 | Top-N、分頁、存在性檢查、抽樣 | 重複刪除、分批處理、快速重訪、IOT |
| 跨查詢持久 | 否 | 是 (除非表移動) |
七、 避坑指南
- ❌
WHERE rownum = 5 / WHERE rownum > 5 → 永遠空結果
- ❌ 以為 ROWNUM 反映表中物理順序 → **無關**，取決於執行計畫存取路徑
- ❌ 將 ROWID 儲存於應用快取長期使用 → 表 MOVE/匯出入後失效
- ✅ 分頁查詢**必須**三層巢狀 (內層排序/過濾 → 中層 ROWNUM 截取 → 外層篩選)
- ✅ 12c+ 優先使用
OFFSET/FETCH 標準語法
- ✅ 大表分批處理優先按
ROWID 範圍或 PK 範圍
ROWNUM 是「結果集的行號」，ROWID 是「資料的身分證」。搞清兩者本質差異、掌握分頁正確模式、善用 ROWID 物理定位優勢，是 Oracle 開發者進階的必修課。
- 上一篇文章： Oracle 專家實戰筆記：Bitmap 索引鎖定災難與 Function-based 索引破解之道
- 下一篇文章： Oracle DATE 型別完整操作指南：格式轉換、時間成分、NLS 參數與 JSP 整合陷阱
Copyright ©
程式碼之家 All Rights Reserved
