Oracle Rowid 与 Rownum 伪列

适用版本：Oracle Database 8i / 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

伪列 是特殊的列，行为类似列但不存储[1]：

伪列	说明
ROWID	行物理地址
ROWNUM	行号
LEVEL	层级
NEXTVAL/CURRVAL	序列值
ORA_ROWSCN	行的 SCN

---

2. ROWID

2.1 结构

ROWID 格式（Extended）：
OOOOOO FFF BBBBBB RRR
6位    3位 6位    3位

- OOOOOO: 数据对象号
- FFF: 数据文件号
- BBBBBB: 块号
- RRR: 行号

2.2 查看

SELECT rowid, employee_id, last_name FROM employees;
-- AAAVu5AAEAAAABSAAA

2.3 DBMS_ROWID

SELECT 
  DBMS_ROWID.ROWID_OBJECT(rowid) AS object_id,
  DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) AS file_id,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) AS block_id,
  DBMS_ROWID.ROWID_ROW_NUMBER(rowid) AS row_num
FROM employees;

2.4 应用

快速访问

-- 通过 ROWID 快速定位
SELECT * FROM employees WHERE rowid = 'AAAVu5AAEAAAABSAAA';

去重

-- 保留每组一条
DELETE FROM employees 
WHERE rowid NOT IN (
  SELECT MIN(rowid) FROM employees GROUP BY email
);

自连接

-- 同表对比
SELECT a.last_name, b.last_name
FROM employees a, employees b
WHERE a.dept_id = b.dept_id
  AND a.rowid < b.rowid;

---

3. ROWNUM

3.1 基本用法

SELECT rownum, employee_id, last_name 
FROM employees;
-- 1, 100, Smith
-- 2, 101, Jones
-- ...

3.2 Top N

SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

3.3 分页

-- 第 11-20 条
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM employees ORDER BY salary DESC
  ) a WHERE ROWNUM <= 20
) WHERE rn > 10;

3.4 ROWNUM 陷阱

-- ROWNUM 在 ORDER BY 之前
SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;
-- 先取 5 行再排序（错误）

-- 正确
SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 5;

3.5 ROWNUM = N

-- ROWNUM = 1 可以
SELECT * FROM employees WHERE ROWNUM = 1;

-- ROWNUM > 1 不能
SELECT * FROM employees WHERE ROWNUM > 1;  -- 无结果

-- ROWNUM = 5 不能
SELECT * FROM employees WHERE ROWNUM = 5;  -- 无结果

---

4. 12c+ FETCH FIRST

4.1 替代 ROWNUM

-- Top N
SELECT * FROM employees 
ORDER BY salary DESC 
FETCH FIRST 10 ROWS ONLY;

-- 跳过前 10
SELECT * FROM employees 
ORDER BY salary DESC 
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- 百分比
SELECT * FROM employees 
ORDER BY salary DESC 
FETCH FIRST 10 PERCENT ROWS ONLY;

-- WITH TIES
SELECT * FROM employees 
ORDER BY salary DESC 
FETCH FIRST 10 ROWS WITH TIES;

4.2 优势

• 标准 SQL
• 简洁
• 支持 OFFSET
• 支持 PERCENT

---

5. ORA_ROWSCN

5.1 行的 SCN

SELECT employee_id, ORA_ROWSCN FROM employees;
-- 显示每行最后修改的 SCN

5.2 转时间

SELECT 
  employee_id,
  ORA_ROWSCN,
  SCN_TO_TIMESTAMP(ORA_ROWSCN) AS change_time
FROM employees;

5.3 依赖 ROWDEPENDENCIES

-- 表需创建时指定
CREATE TABLE employees (
  ...
) ROWDEPENDENCIES;

-- 否则 ORA_ROWSCN 为块级

---

6. LEVEL

-- 层次查询
SELECT 
  LPAD(' ', LEVEL*2-2) || last_name AS name,
  LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

详细见：Oracle 层次查询（Hierarchical Query）。

---

7. 序列伪列

-- NEXTVAL
SELECT seq_emp.NEXTVAL FROM dual;

-- CURRVAL
SELECT seq_emp.CURRVAL FROM dual;

-- 使用
INSERT INTO employees (id, name) 
VALUES (seq_emp.NEXTVAL, 'Alice');

详细见：Oracle 序列（Sequence）与同义词（Synonym）。

---

8. 常见坑与排错

8.1 ROWNUM 与 ORDER BY

-- 错误：ROWNUM 在 ORDER BY 之前
SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;

-- 正确：子查询
SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 5;

8.2 ROWID 不稳定

-- 表重建后 ROWID 变化
-- 不能作为长期标识

8.3 ROWNUM > 1 无结果

-- ROWNUM 是递增分配
-- 必须 >= 1 开始
-- 用子查询包装
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM employees a
) WHERE rn > 5;

---

9. 最佳实践

1. ROWID 用于快速访问：性能
2. ROWID 用于去重：高效
3. ROWNUM 用于 Top N：注意顺序
4. 12c+ 用 FETCH FIRST：标准
5. 分页用子查询：避免 ROWNUM 陷阱
6. 不依赖 ROWID 长期：会变化
7. ORA_ROWSCN 追踪修改：审计

---

10. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Pseudocolumns” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Pseudocolumns.html