Oracle有两个常用的伪列rowid和rownum,对伪列的操作类似于表中的列,你可以对其进行查询操作,但是你却不能对其进行增加、修改或者是删除。

一、rowid伪列

1、rowid的原理

Oracle数据库表中的每一行在数据库中有一个存放的位置,即rowid(row identification),rowid的长度是10个字节,显示的结果是18字节的字符串,该值表明了行在Oracle数据库中存放的物理具体位置,是唯一的,Oracle内部通常使用它来访问数据。

我们从超女表中查询每行的rowid。

image.png

rowid虽然不是表的字段,但是,在Oracle数据库的内部,保存了每行数据的rowid。

rowid使用base64编码行的物理地址,编码字符包含A-Z, a-z, 0-9, +, 和/,由四部分组成:OOOOOOOFFFBBBBBBRRR,如下图:

image.png

1)对象编号:行所属数据库对象的编号,每个数据库对象在创建的时候都被分配一个唯一的编号,六位显示。

2)文件编号:行所在数据文件的编号,每一个文件标号也是唯一的,三位显示。

3)块编号:行所在数据文件的数据块的位置,六位显示。

4)行编号:行目录中的具体位置,三位显示。

Oracle正是根据rowid可以准确的定位存放行数据的物理位置。

2、rowid数据类型

在Oracle数据库中,用rowid数据类型存放rowid的值,长度是10个字节,为了方便查看和书写,Oracle可以把10字节的rowid类型和18字节的字符串自动转换。

对程序员来说,把rowid当成18字节的字符串就行了。

image.png

二、rowid的应用

我以一个程序员的视角来向大家介绍rowid的应用场景。

1、记录表数据的变化

例如我希望把超女基本信息表T_GIRL的操作日志(增加、修改和删除)记录下来。

1)创建一个SQL日志表

create table T_SQL_LOG
(
  tname    varchar2(30),        -- 原表的表名。
  srcrowid rowid,               -- 原表rowid。
  sqltype  number(1),           -- SQL语句的类型:1-insert、2-update、3-delete。
  crttime  date default sysdate -- 操作时间。
);

2)在原表中创建触发器

在超女基本信息表T_GIRL上创建触发器。

create or replace trigger TR_GIRL_LOG
       after insert or update or delete
       on T_GIRL for each row
begin
  if inserting then
    insert into T_SQL_LOG(tname,srcrowid,sqltype) values('T_GIRL',:new.rowid,1);
  end if;
  if updating then
    insert into T_SQL_LOG(tname,srcrowid,sqltype) values('T_GIRL',:new.rowid,2);
  end if;
  if deleting then
    insert into T_SQL_LOG(tname,srcrowid,sqltype) values('T_GIRL',:old.rowid,3);
  end if;
end;

3)测试效果

如果对T_GIRL表插入了四条记录,在T_SQL_LOG中记录的内容如下:

image.png

4)应用经验

T_SQL_LOG日志表中可以找到原表数据变化的情况,这种处理技巧在数据审计和数据同步中经常用到。

2、用于where的条件

rowid是记录了数据存放的物理位置,用rowid作为where的条件的效率永远是最高的,远远超过任何索引,利用这个特点可以提高数据操作的效率。

3、rowid的缺点

rowid的效率虽然很高,在实际开发中经常用到,但也有缺点,主要有两方面:

1)兼容性不好,rowid是Oracle数据库特有的,其它数据库没有,如果你的应用程序需要迁移到其它数据库,就不能用rowid了。

2)rowid记录的是数据存放的物理位置,这个值在数据整理、数据迁移和数据备份的时候会变化。

三、rownum伪列

首先,rowid和rownum都是oracle的伪列,但原理和用法完全不同,没有任何关联和可比性,不要用学习rowid的经验来学习rownum。

rownum(row number)是Oralce为查询结果集的行分配的顺序号,例如:

image.png

image.png

再说一次,rownum很简单,就是Oralce为查询结果集的行分配的顺序号。

rownum在数据库中没有存储空间,从以上的两个查询可以看出,在第一个查询结果中,妲已的rownum是3,在第二个查询结果集中,妲已的rownum是1。

如果你还不明白,我再解释一次,rownum是Oralce为查询结果集的行分配的顺序号。

四、rownum的应用

1、限制数据库大事务和长事务

用delete删除表中数据的时候,如果数据记录比较多,会产生大事务和长事务,为了避免对数据库的冲击,可以用rownum限制每次delete数据的记录数。

delete from 表名 where 待删除数据的条件 and rownum<=100000 ;

以上delete语句一次删除100000条记录,这种用法适用于update语句。

2、用于筛选查询结果集的记录

筛选查询结果集的记录的用法对C/C++程序员意义不大,但对java程序员来说非常重要,主要用于查询结果的分页,各位有兴趣可以看一下。

1)rownum 对于等于某值的查询条件

如果希望找到超女表中第一条超女的信息,可以使用rownum=1作为条件。但是想找到超女表中第二条超女的信息,使用rownum=2结果查不到数据。因为rownum都是从1开始,但是1以上的自然数在rownum做等于判断是时认为都是false条件,所以无法查到rownum = n(n>1的自然数)。

select rownum,id,name from T_GIRL where rownum=1; --有记录

select rownum,id,name from T_GIRL where rownum=2; --无记录

image.png

2)rownum对于大于某值的查询条件

如果想找到从第二行记录以后的记录,当使用rownum>2是查不出记录的,可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名,否则还是不会查出记录来,这是因为rownum不是某个表的列,如果不起别名的话,无法知道rownum是子查询的列还是主查询的列。

select * from (select rownum no,id,name from T_GIRL) where no>2;      -- 有记录

select * from (select rownum   ,id,name from T_GIRL) where rownum>2;  -- 无记录

image.png

3)rownum对于小于某值的查询条件

如果想找到第三条记录以前的记录,当使用rownum<3是能得到两条记录的。显然rownum对于rownum1的自然数)的条件认为是成立的,所以可以找到记录。

select rownum,id,name from T_GIRL where rownum<3; -- 有记录

image.png

4)rownum排序

Oracle中的rownum的是在从数据库中取数据的时候产生的序号,所以想对指定排序的数据去指定的rowmun行数据就必须注意了。

select rownum,id,name from T_GIRL order by name;

image.png

可以看出,rownum并不是按照name列来生成的序号。系统是按照记录从数据库取出的顺序给记录排的号,为了解决这个问题,必须使用子查询。

select rownum,id,name from (select * from T_GIRL order by name);

image.png

五、小结

rownum和rowid都是伪列,但两者的原理是不同的,rownum是根据SQL查询出来的结果给每行分配一个临时编号,不同的SQL会导致rownum不同。rowid是物理存在的,在每条记录insert到数据库时,就会有一个唯一的物理位置,记录了在数据库中的位置ID,只要记录没被搬动过,rowid是不变的。

rownum常用于分页查询,rowid常用于提高数据操作的效率,在某些场景中,还会把rowid当成主建来使用。

六、版权声明

C语言技术网原创文章,转载请说明文章的来源、作者和原文的链接。

来源:C语言技术网(www.freecplus.net

作者:码农有道

如果文章有错别字,或者内容有错误,或其他的建议和意见,请您联系我们指正,非常感谢!!!


C语言技术网(www.freecplus.net),粤ICP备19156379号

友情链接: 猿说编程    程序分享   

点击关闭