ROWID与ROWNUM的简介与对比

浏览： ℃

字体：大中小

发布时间：2013-12-12 14:50:51

来源：

关于ROWID：

在用户向表中插入一行数据时，ORACLE会自动在这一行数据加上一个ROWID，每行都有一个唯一ROWID，ORACLE利用ROWID定位数据行。ROWID并不显式存储为一列的值(伪列--不是存在表中的实际数据，可能是内部采用函数什么的根据行所在块的信息转换的），是访问一个表中行的最快机制。索引中存储的有索引行的值及索引行的ROWID的值--实际数据。

ORACLE ROWID分为物理ROWID，逻辑ROWID。
详见：通过rowid得到数据块的相关信息

关于ROWNUM:

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。

ROWNUM的使用示例总结如下：

使用=时，只有rownum=1有用，=其它数值将返回空集。
使用<和<=时，能返回所需的行。
使用>和>=时，只有>=1时返回全表数据，其它只能返回空集。rownum对于大于某值的查询条件，使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录
小于与小于等于：
BYS@ bys3>select * from dept where rownum<2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
BYS@ bys3>select * from dept where rownum<=2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS

大于与大于等于：
BYS@ bys3>select * from dept where rownum>=1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
BYS@ bys3>select * from dept where rownum>1;
no rows selected
BYS@ bys3>select * from dept where rownum>2;
no rows selected
BYS@ bys3>select * from dept where rownum>=2;
no rows selected

等于：
BYS@ bys3>select * from dept where rownum=1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
BYS@ bys3>select * from dept where rownum=2;
no rows selected

不等于：--条件不成立返回空集
BYS@ bys3>select * from dept where rownum<>1;
no rows selected
#############

ROWNUM与ROWID在DML操作中的变化示例：

系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。
rownum 表示查询某条记录在整个结果集中的位置，在查询的结果集中顺序分配的。
1.查询ROWID与ROWID中的行号、ROWNUM
BYS@ bys3>select rowid,dbms_rowid.rowid_row_number(rowid) rowid_num,rownum,dept.* from dept order by deptno desc;
ROWID ROWID_NUM ROWNUM DEPTNO DNAME LOC
------------------ ---------- ---------- ---------- -------------- -------------
AAAFT7AAEAAAAIFAAD 3 4 40 OPERATIONS BOSTON
AAAFT7AAEAAAAIFAAC 2 3 30 SALES CHICAGO
AAAFT7AAEAAAAIFAAB 1 2 20 RESEARCH DALLAS
AAAFT7AAEAAAAIFAAA 0 1 10 ACCOUNTING NEW YORK
以其中一行记录为例分析：
AAAFT7AAEAAAAIFAAC 2 3 30 SALES CHICAGO
这一行的ROWID是AAAFT7AAEAAAAIFAAC ，按ROWID的算法，可以得出数据块中第2行
但是此行的ROWNUM是3，是在查询出的结果集中的排序。很直观的对比出ROWID中一行数据在数据块中的第几行与ROWNUM的号码不是同一事物。
2.删除一条数据
BYS@ bys3>delete dept where deptno=30;
1 row deleted.
BYS@ bys3>commit;
Commit complete.
3.查询ROWID与ROWID中的行号、ROWNUM。发现ROWID与ROWID中的行号已经删除，但是ROWNUM被自动顺序分配了。
BYS@ bys3>select rowid,dbms_rowid.rowid_row_number(rowid) rowid_num,rownum,dept.* from dept order by deptno desc;
ROWID ROWID_NUM ROWNUM DEPTNO DNAME LOC
------------------ ---------- ---------- ---------- -------------- -------------
AAAFT7AAEAAAAIFAAD 3 3 40 OPERATIONS BOSTON
AAAFT7AAEAAAAIFAAB 1 2 20 RESEARCH DALLAS
AAAFT7AAEAAAAIFAAA 0 1 10 ACCOUNTING NEW YORK
4.插入一条数据
BYS@ bys3>insert into dept values(99,'chedan','bj');
1 row created.
BYS@ bys3>commit;
Commit complete.
5.查询ROWID与ROWID中的行号、ROWNUM。发现ROWID与ROWID中的行号自动向下分配而不是重用第3步中删除的行的ROWID。ROWNUM依然自动顺序分配
BYS@ bys3>select rowid,dbms_rowid.rowid_row_number(rowid) rowid_num,rownum,dept.* from dept order by deptno desc;
ROWID ROWID_NUM ROWNUM DEPTNO DNAME LOC
------------------ ---------- ---------- ---------- -------------- -------------
AAAFT7AAEAAAAIFAAE 4 4 99 chedan bj
AAAFT7AAEAAAAIFAAD 3 3 40 OPERATIONS BOSTON
AAAFT7AAEAAAAIFAAB 1 2 20 RESEARCH DALLAS
AAAFT7AAEAAAAIFAAA 0 1 10 ACCOUNTING NEW YORK

关于不同的排序结果使用ROWNUM，ROWNUM显示会不会变化？实验结果是不会改变的

BYS@ bys3>select rownum,dept.* from dept;

ROWNUM DEPTNO DNAME LOC
---------- ---------- -------------- -------------
1 10 ACCOUNTING NEW YORK
2 20 RESEARCH DALLAS
3 40 OPERATIONS BOSTON
4 99 chedan bj
BYS@ bys3>select rownum,dept.* from dept order by loc;
ROWNUM DEPTNO DNAME LOC
---------- ---------- -------------- -------------
3 40 OPERATIONS BOSTON
2 20 RESEARCH DALLAS
1 10 ACCOUNTING NEW YORK
4 99 chedan bj

查表中最后一行记录的方法：

BYS@ bys3>select * from dept where rowid in(select max(rowid) from dept);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
BYS@ bys3>select * from dept where rownum<=(select count(*) from dept) minus select * from dept where rownum<=(select count(*)-1 from dept);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON

>更多相关文章