Oracle RowID与RowNum的区别

Rowid和Rownum对于数据库开发人员来说基本很少用到，因为在企业数据库开发中大多都是进行数据批处理，但是对于其他数据库人员来说还是会用到的。
rowid和rownum都是虚列，但含义完全不同。rowid是物理地址，用于定位oracle中具体数据的物理存储位置，而rownum则是sql的输出结果排序。通俗的讲：rowid是相对不变的，rownum会变化，尤其是使用order by的时候。

 

rowid 用于定位数据表中某条数据的位置，是唯一的、也不会改变

 

rownum 表示查询某条记录在整个结果集中的位置， 同一条记录查询条件不同对应的 rownum 是不同的而 rowid 是不会变的

   

例如有如下一张表 :USER  

name	age
张三	20
李四	22
王五	23

 

当执行查询 : select  rowid ,rownum ,name,age   from USER order by age asc 时结果如下：  

rowid	rownum	name	age
AAAOWhAAQAAALgdAAa	1	张三	20
AAAOWhAATAAALp7AAd	2	李四	22
AAAOWhAATAAALp7AAe	3	王五	23

   

当执行查询 : select  rowid ,rownum ,name,age   from USER order by age desc 时结果如下：  

rowid	rownum	name	age
AAAOWhAATAAALp7AAe	1	王五	23
AAAOWhAATAAALp7AAd	2	李四	22
AAAOWhAAQAAALgdAAa	3	张三	21

 

ROWID： 
1、为什么使用ROWID 　　

ORACLE把ROWID作为B-树和其内部算法标示ROW的唯一标示。在ORACLE8以前的版本中，ROWID标示FILE、BLOCK,ROW NUMBER，只用一个数字代表FILE号。

在ORACLE8中，一个DATAFILE有两个数字代表：

1.)一个绝对值，是整个数据库唯一的。可以看DBA_DATA_FILES中的FILE_ID。

 

SQL> SELECT FILE_ID FROM DBA_DATA_FILES;
   FILE_ID
----------
         4
         3
         2
         1

　

 

2.)一个相对值，在TABLESPACE中是唯一的，可以看DBA_DATA_FILES中的RELATIVE_FNO。

 

SQL> SELECT RELATIVE_FNO FROM DBA_DATA_FILES;
RELATIVE_FNO
------------
           4
           3
           2
           1

新的ROWID使用相对值，所以必须存放SEGMENT的标示，否则就会混淆。所以ORACLE8在ROWID中加入对象的SEGMENT号，用来标示TABLE或者PARTITION。

2、ROWID的结构 　

 

使用base-64代码，包括a-z，A-Z，0-9，+,-。一共18位。 　　1-6位：代表OBJECT 　　7-9位：文件相对值 　　10-15：文件中的BLOCK 　　16-18：BLOCK中的SLOT值

　　3、TABLESPACE-Relative寻址方式　　使用的是TABLESPACE-Relative寻址方式，多个文件可以有相同的相对值，因为它　　们属于不同的TABLESPACE，所以不能从新的ROWID得到绝对地址，但是这没有问题　　，因为当要处理某个OBJECT时，已经能确定它属于哪个TABLESAPCE了。在TABLES 　　PACE中，文件相对值是唯一的，所以ROWID还是可以唯一标示一个OBJECT。TABLE 　　SPACE-Relative寻址方式是ORACLE8中支持超大数据库的关键技术。

　　4、DATA OBJECT NUMBER 　　DATA OBJECT NUMBER用于指示SEGMENT，所有SEGMENT都有DATA OBJECT NUMBER，存放在每个DATA BLOCK中，而且不重复。

　　最开始的时候，DBA_OBJECTS.OBJECT_ID=DBA_OBJECTS.DATA-OBJECT_ID，但是在上述情况下DATA- OBJECT_ID会在如下情况下增加 　　TRUNCATE TABLE 　　MOVE PARTITION 　　ORACLE会检查ROWID中的DATA OBJECT NUMBER和BLOCK中的DATA OBJECT NUMBER，保证他们之间的版本是一致的。 　　ORACLE也使用DATA OBJECT NUMBER以确保ROLLBACK的纪录和最新的SEGMENT纪录一致。 　　要注意的是DATA OBJECT NUMBER不是OBJECT 的标志

　　　　5、RESTRICTED ROWID 　　ORACLE7的ROWID格式是 　　1-8位：BLOCK NUMBER 　　9-12位：ROW NUMBER 　　13-16位：FILE NUMBER 　　ORACLE8支持短的、旧格式的ROWID，作用是　　对NOPARTITION TABLE的INDEX ENTRY 　　对PARTITION TABLE的LOCAL INDEX ENTRY 　　ROW Piece CHain pointer 　　受限ROWID的内部存放是6BYTE， 　　4BYTE=DATA BLOCK NUMBER 　　2BYTE=ROW NUMBER 　　这就是说，INDEX ENTRY使用6BYTE存放该ROWID，这对大多数INDEX足够了。但是这种短ROWID不能使用在PATITION TABLE的GLOBAL INDEX上，因为PARTITION可能跨TABLESPACE。显示这种ROWID依然是18位的

　　6、扩展的ROWID 　　ORACLE在内部存放时候是10 BYTE，包括（DATA OBJECT NUMBER，DATA BLOCK NU 　　MBER，ROW NUMBER） 　　ORACLE8使用扩展的ROWID： 　　PARTITION TABLE 的GLOBAL INDEX 　　SERVER 算法 　　扩展的ROWID在SELECT时，依然是18位的显示，存放在ROWID字段中。

　　7、在ORACLE8中使用 ORACLE7的ROWID 　　从ORACLE8的DB中查询ORACLE7的ROWID时候，ROWID返回的是ORACLE7的格式，也可以用在WHERE语句中。　　从ORACLE7的DB中查询ORACLE8的ROWID时候，ROWID返回的是ORACLE8的格式，也可以用在WHERE语句中，但是不能存放在ROWID字段中。但是你要用DBMS_ROWID 包来解释之。 　　如果包含扩展的ORACLE8 ROWID，这不能把ORACLE8的数据IMPORT到ORACLE7中。从ORACLE7中可以IMPORT到ORACLE8中。

　　 8、APPLICATION的移植问题 　　一般程序的移植应该没有问题。只有在下面情况下才考虑移植问题：　　application使用了rowid 　　table包括ROWID类型的字段 　　如果程序有如下情况，必须使用DBMS_ROWID包：　　自己组合ROWID 　　自己分解ROWID 　　如果仅仅是传递ROWID到变量、或者仅仅做为一个整体使用，则可以不受影响。

　　　　9、数据的移植问题 　　无论使用EXPORT/IMPORT还使用移植工具，ORACLE7中的ROWID字段到了ORACLE8中就自动扩展。如果在某个字段内容中包含ROWID，则必须手工用DBMS_ROWID包来转换。

　　10、DBMS_ROWID包　　由$ORACLE_HOME/rdbms/admin/dbmsutil.sql创建，其实在catproc.sql中包含着。提供处理ROWID的一些函数。 　　ROWID_CREATE 　　ROWID_INFO 　　ROWID_TYPE 　　ROWID_OBJECT 　　ROWID_RELATIVE_FNO 　　ROWID_BLOCK_NUMBER 　　ROWID_TO_ABSOLUTE_FNO 　　ROWID_TO_EXTENDED 　　ROWID_TO_RESTRICTED 　　ROWID_VERIFY

　　　　 DBMS_ROWID.ROWID_TO_EXTENDED 　　(old_rowid in ROWID, 　　schema_name in varchar2, 　　object_name in varchar2, 　　conversion_type in number 　　) 　　RETURN ROWID; 　　转换受限rowid到扩展rowid，用于转换旧的ROWID到ORACLE8的格式。

　　　　DBMS_ROWID.ROWID_TO_RESTRICTED 转换扩展的ROWID到受限的ROWID。

　　DBMS_ROWID.ROWID_VERIFY 　　判断一个受限的ROWID是否可以转换到扩展的格式

　　　　DBMS_ROWID.ROW_INFO 　　用于解释ROWID，可以得到DATA OBJECT NUMBER，RELATIVE FILE NUMBER,BLOCK NUMBER和ROW NUMBER。

　　DBMS_ROWID.CREATE 　　生成ROWID。

ROWNUM：

在Oracle中，要按特定条件查询前N条记录，用个rownum就搞定了。 select * from emp whererownum<= 5 而且书上也告诫，不能对rownum用">"，这也就意味着，如果你想用 select * from emp whererownum> 5 则是失败的。要知道为什么会失败，则需要了解rownum背后的机制： 1 Oracle executes your query.

2 Oracle fetches the first row and calls it row number 1.

3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.

4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).

5 Go to step 3.

了解了原理，就知道rownum>不会成功，因为在第三步的时候查询出的行已经被丢弃，第四步查出来的rownum仍然是1,这样永远也不会成功。

同样道理，rownum如果单独用=,也只有在rownum=1时才有用。

 

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。  举例说明：例如表：student(学生)表，表结构为： ID　　　    char(6)　　　　　 --学号 name　　　　VARCHAR2(10)　　　--姓名 create table student (ID char(6), name VARCHAR2(100)); insert into sale values('200001',‘张一’); insert into sale values('200002',‘王二’); insert into sale values('200003',‘李三’); insert into sale values('200004',‘赵四’); commit; (1) rownum对于等于某值的查询条件如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum= n（n>1的自然数）。 SQL> selectrownum,id,name from student whererownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标） SQL> selectrownum,id,name from student whererownum=1;    ROWNUMID     NAME ---------- ------ ---------------------------------------------------          1 200001 张一 SQL> selectrownum,id,name from student whererownum=2;    ROWNUMID     NAME ---------- ------ --------------------------------------------------- （2）rownum对于大于某值的查询条件    如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录 SQL> selectrownum,id,name from student whererownum>2;ROWNUMID     NAME ---------- ------ --------------------------------------------------- 那如何才能找到第二行以后的记录呀。可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。 SQL>select * from(selectrownumno ,id,name from student) where no>2;         NO ID     NAME ---------- ------ ---------------------------------------------------          3 200003 李三          4 200004 赵四 SQL> select * from(selectrownum,id,name from student)whererownum>2;    ROWNUMID     NAME ---------- ------ --------------------------------------------------- （3）rownum对于小于某值的查询条件如果想找到第三条记录以前的记录，当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。 SQL> selectrownum,id,name from student whererownum<3;    ROWNUMID     NAME ---------- ------ --------------------------------------------------- 1 200001 张一         2 200002 王二综上几种情况，可能有时候需要查询rownum在某区间的数据，那怎么办呀从上可以看出rownum对小于某值的查询条件是人为true的，rownum对于大于某值的查询条件直接认为是false的，但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句，先让它返回小于等于三的记录行，然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。 SQL> select * from (selectrownumno,id,name from student whererownum<=3 ) where no >=2;         NO ID     NAME ---------- ------ ---------------------------------------------------          2 200002 王二          3 200003 李三（4）rownum和排序 Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。 SQL> selectrownum,id,name from student order by name;    ROWNUMID     NAME ---------- ------ ---------------------------------------------------          3 200003 李三          2 200002 王二          1 200001 张一          4 200004 赵四可以看出，rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。为了解决这个问题，必须使用子查询 SQL> selectrownum,id,name from (select * from student order by name);    ROWNUMID     NAME ---------- ------ ---------------------------------------------------          1 200003 李三          2 200002 王二          3 200001 张一          4 200004 赵四这样就成了按name排序，并且用rownum标出正确序号（小到大）