oracle 中的伪列和表

1、oracle的伪列

oracle系统为了实现完整的关系数据库功能，系统专门提供了一组称为伪列（Pseudocolumn）的数据库列，这些列不是在建立对象时由我们完成的，而是在我们建立时由Oracle完成的。Oracle目前常见的伪列： 

currval 和 nextval 使用序列号的保留字 

        CURRVAL：返回当前sequence值

        NEXTVAL：增加sequence并返回下一个值

 一般用法：

  sequence.CURRVAL

  sequence.NEXTVAL

level 查询数据所对应的级 

     分级查询是oracle提供的递归查询语法，在这里不做展开。只有在分级查询下，才可以使用以下伪列：

       CONNECT_BY_ISCYCLE Pseudocolumn

        CONNECT_BY_ISLEAF Pseudocolumn

        LEVEL Pseudocolumn

Version Query Pseudocolumns

Version Query伪列只有在Flashback Version Query时才有效，内容如下：

 

        VERSIONS_STARTSCN and VERSIONS_STARTTIME

        VERSIONS_ENDSCN and VERSIONS_ENDTIME

        VERSIONS_XID

        VERSIONS_OPERATION

 

l        COLUMN_VALUE Pseudocolumn

l        OBJECT_ID Pseudocolumn

l        OBJECT_VALUE Pseudocolumn

l        ORA_ROWSCN Pseudocolumn

l        XMLDATA Pseudocolumn

  rowid 记录的唯一标识 
  rownum 限制查询结果集的数量 

在对普通表做查询时，比较常用的伪列有：ORA_ROWSCN、ROWID、ROWNUM。

ORA_ROWSCN

虽然叫ORA_ROWSCN，不过默认情况下，查询出的该值是从数据文件块头获取的，也就是说，查询出的是block的最近事务的scn，而不是精确到row的scn。

在创建表时，可以指定ROWDEPENDENCIES来使ora_rowscn真正记录行一级的scn。

 

看一下ROWDEPENDENCIES 的定义：Specify ROWDEPENDENCIES if you want to enable row-level dependency tracking（追踪）. This setting is useful primarily to allow for parallel propagation in replication environments. It increases the size of each row by 6 bytes.

从这里也可以看出一个SCN占用的空间（6 bytes）。SCN的最大值是0xffff.ffffffff，共48位。包括2bytes的高位字节（SCN wrap）和4bytes的低位字节（SCN base）。

 

SQL>select ora_rowscn,username from t;

ORA_ROWSCN USERNAME

---------- ------------------------------

 86516279 SYSTEM

 86516279 SYS

 86516279 OUTLN

 86516279 LINCINQ

 86516279 TEST

 86516279 LINC

 86516279 SPLEXUC

 86516279 DIP

  86516279 TSMSYS

 86516279 WMSYS

 86516279 DBSNMP

11 rows selected.

 

需要查询scn对于的具体时间，可以用函数SCN_TO_TIMESTAMP

 

SQL>alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';

 Session altered.

 

SQL>select scn_to_timestamp(ora_rowscn),username from t;

 SCN_TO_TIMESTAMP(ORA_ROWSCN)  USERNAME

------------------------------ ------------------------------

2011-06-01 13:13:31           SYSTEM

2011-06-01 13:13:31           SYS

2011-06-01 13:13:31           OUTLN

2011-06-01 13:13:31           LINCINQ

2011-06-01 13:13:31           TEST

2011-06-01 13:13:31           LINC

2011-06-01 13:13:31           SPLEXUC

2011-06-01 13:13:31           DIP

2011-06-01 13:13:31           TSMSYS

2011-06-01 13:13:31           WMSYS

2011-06-01 13:13:31           DBSNMP

 

11 rows selected.

 

 

ROWID

 

rowid是一种特殊类型的列，又叫做rowid伪列。rowid伪列可以向正常列一样使用sql select语句访问。oracle数据库每一行都有一个rowid伪列。rowid表示特定行的特定地址。rowid 伪列可以用rowid数据类型来定义。 
rowid与磁盘驱动器中的特定位置相关。因此,rowid是获取某个行最快速的方法。然面，一个行的rowid会随着数据库的卸载和重新加载而发生变化。基于这一点考虑，我们不推荐在多个事务代码中使用rowid伪列的值。     用户无法使用sql语句来设置标准的rowid伪列的数值。     用户可以定义rowid类型的列或者变量，但是oracle并不保证存在这些列或者变量中的数值就是有效的rowid 
   rowid就是表记录存在于文件系统中的物理位置，索引结构中包含rowid，因此通过索引能快速的定位表中的记录. rowid的存在时是了能根据它找到表的这条记录存在哪个具体的物理位置，我们需要知道它在哪个数据文件，在哪个block，在哪一行 
这就是修改某列时需要提供rowid的原因。 
Rowid的概念：rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。

Rowid是基于64位编码的18个字符，格式如下：

data_object_id     file_id      block_number  row_number

----------       ----------     ----------      ----

  OOOOOO            FFF          BBBBBB         RRR

 

SQL>select rowid,username from t;

 

ROWID             USERNAME

------------------ ------------------------------

AAAE0LAAOAABQqMAAA SYSTEM

AAAE0LAAOAABQqMAAB SYS

AAAE0LAAOAABQqMAAC OUTLN

AAAE0LAAOAABQqMAAD LINCINQ

AAAE0LAAOAABQqMAAE TEST

AAAE0LAAOAABQqMAAF LINC

AAAE0LAAOAABQqMAAG SPLEXUC

AAAE0LAAOAABQqMAAH DIP

AAAE0LAAOAABQqMAAI TSMSYS

AAAE0LAAOAABQqMAAJ WMSYS

AAAE0LAAOAABQqMAAK DBSNMP

 

11 rows selected.

 

通过dbms_rowid包，我们可以获得rowid对应的记录详细信息。

 

SQL>select dbms_rowid.rowid_object('&1') data_object_id#,

        dbms_rowid.rowid_relative_fno('&1') rfile#,

        dbms_rowid.rowid_block_number('&1') block#,

        dbms_rowid.rowid_row_number('&1') row# from dual;

Enter value for 1: AAAE0LAAOAABQqMAAA

old  1: select dbms_rowid.rowid_object('&1') data_object_id#,

new  1: select dbms_rowid.rowid_object('AAAE0LAAOAABQqMAAA') data_object_id#,

Enter value for 1: AAAE0LAAOAABQqMAAA

old  2:       dbms_rowid.rowid_relative_fno('&1') rfile#,

new  2:       dbms_rowid.rowid_relative_fno('AAAE0LAAOAABQqMAAA') rfile#,

Enter value for 1: AAAE0LAAOAABQqMAAA

old  3:       dbms_rowid.rowid_block_number('&1') block#,

new  3:       dbms_rowid.rowid_block_number('AAAE0LAAOAABQqMAAA') block#,

Enter value for 1: AAAE0LAAOAABQqMAAA

old  4:       dbms_rowid.rowid_row_number('&1') row# from dual

new  4:       dbms_rowid.rowid_row_number('AAAE0LAAOAABQqMAAA') row# from dual

 

DATA_OBJECT_ID#    RFILE#    BLOCK#      ROW#

--------------- ---------- ---------- ----------

         19723        14    330380         0

 

ROWNUM

 

在对表的查询中，每返回一条记录，rownum伪列就返回一个数字，代表查询返回的行的编号。

 

SQL>select rownum,username from t;

 

   ROWNUM USERNAME

---------- ------------------------------

        1 SYSTEM

        2 SYS

        3 OUTLN

        4 LINCINQ

        5 TEST

        6 LINC

        7 SPLEXUC

        8 DIP

        9 TSMSYS

       10 WMSYS

       11 DBSNMP

 

11 rows selected.

 

从下面的例子可以看出，rownum返回的是查询过程中返回记录的顺序，并不是查询结果的序列号。

 

SQL>select rownum,username from t order by username;

 

   ROWNUM USERNAME

---------- ------------------------------

       11 DBSNMP

        8 DIP

        6 LINC

        4 LINCINQ

        3 OUTLN

        7 SPLEXUC

        2 SYS

        1 SYSTEM

        5 TEST

        9 TSMSYS

       10 WMSYS

 

11 rows selected.

 

SQL>select rownum,username from (select username from t order by username);

 

   ROWNUM USERNAME

---------- ------------------------------

        1 DBSNMP

        2 DIP

        3 LINC

        4 LINCINQ

        5 OUTLN

        6 SPLEXUC

        7 SYS

        8 SYSTEM

        9 TEST

       10 TSMSYS

       11 WMSYS

 

11 rows selected.

为什么Oracle的伪列rownum不支持>,>=,=,between...and，而都用<   ？

对于 Oracle 的 rownum 问题，很多资料都说不支持>,>=,=,between...and，只能用以上符号(<、<=、!=)，并非说用>,>=,=,between..and 时会提示SQL语法错误，而是经常是查不出一条记录来，还会出现似乎是莫名其妙的结果来，其实您只要理解好了这个 rownum 伪列的意义就不应该感到惊奇，同样是伪列，rownum 与 rowid 可有些不一样，下面以例子说明 ：

假设某个表 t1(c1) 有 20 条记录 
如果用 select rownum,c1 from t1 where rownum < 10, 只要是用小于号，查出来的结果很容易地与一般理解在概念上能达成一致，应该不会有任何疑问的。 

可如果用 select rownum,c1 from t1 where rownum > 10 (如果写下这样的查询语句，这时候在您的头脑中应该是想得到表中后面10条记录)，你就会发现，显示出来的结果要让您失望了，也许您还会怀疑是不谁删了一些记录，然后查看记录数，仍然是 20 条啊？那问题是出在哪呢？ 

先好好理解 rownum 的意义吧。因为ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。所以您没办法期望得到下面的结果集： 

11 aaaaaaaa 
12 bbbbbbb 
13 ccccccc 
................. 

rownum >10 没有记录，因为第一条不满足去掉的话，第二条的ROWNUM又成了1，所以永远没有满足条件的记录。或者可以这样理解： 

ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。如果你用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。 

有了以上从不同方面建立起来的对 rownum 的概念，那我们可以来认识使用 rownum 的几种现像 

1. select rownum,c1 from t1 where rownum != 10 为何是返回前9条数据呢？它与 select rownum,c1 from tablename where rownum < 10 返回的结果集是一样的呢？ 
    因为是在查询到结果集后，显示完第 9 条记录后，之后的记录也都是 != 10,或者 >=10,所以只显示前面9条记录。也可以这样理解，rownum 为9后的记录的 rownum为10，因条件为 !=10，所以去掉，其后记录补上，rownum又是10，也去掉，如果下去也就只会显示前面9条记录了 

2. 为什么 rownum >1 时查不到一条记录，而 rownum >0 或 rownum >=1 却总显示所以的记录 
   因为 rownum 是在查询到的结果集后加上去的，它总是从1开始 

3. 为什么 between 1 and 10 或者 between 0 and 10 能查到结果，而用 between 2 and 10 却得不到结果 
   原因同上一样，因为 rownum 总是从 1 开始 

从上可以看出，任何时候想把 rownum = 1 这条记录抛弃是不对的，它在结果集中是不可或缺的，少了rownum=1 就像空中楼阁一般不能存在，所以你的 rownum 条件要包含到 1 

但如果就是想要用 rownum > 10 这种条件的话话就要用嵌套语句,把 rownum 先生成，然后对他进行查询。 
select * from (selet rownum as rn，t1.* from a where ...) where rn >10 

一般代码中对结果集进行分页就是这么干的。 

另外：rowid 与 rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid 相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum那些情况发生。 



2、oracle的伪表 
dual 
该表主要目的是为了保证在使用SELECT语句中的语句的完整性而提供的。 ---------------------------------------