ROWID与ROWNUM的简介与对比_rownum和rowid的区别与使用

关于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也是顺序分配的。 

--查询时不指定排序字段时， oracle是按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