oracle+伪列

在对表进行查询时，我们可以对oracle提供的一系列伪列（pseudocolumns）进行查询，这些伪列不存储在表中，oracle不允许对伪列进行dml操作。

 

在oracle10g和11g下，伪列包括如下内容：

 

l        Hierarchical Query Pseudocolumns

 

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

 

1.        CONNECT_BY_ISCYCLE Pseudocolumn

2.        CONNECT_BY_ISLEAF Pseudocolumn

3.        LEVEL Pseudocolumn

 

l        Sequence Pseudocolumns

 

Sequence有以下2个伪列：

 

1.        CURRVAL：返回当前sequence值

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

 

一般用法：

 

sequence.CURRVAL

sequence.NEXTVAL

 

l        Version Query Pseudocolumns

 

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

 

1.        VERSIONS_STARTSCN and VERSIONS_STARTTIME

2.        VERSIONS_ENDSCN and VERSIONS_ENDTIME

3.        VERSIONS_XID

4.        VERSIONS_OPERATION

 

l        COLUMN_VALUE Pseudocolumn

l        OBJECT_ID Pseudocolumn

l        OBJECT_VALUE Pseudocolumn

l        ORA_ROWSCN Pseudocolumn

l        ROWID Pseudocolumn

l        ROWNUM Pseudocolumn

l        XMLDATA Pseudocolumn

 

在对普通表做查询时，比较常用的伪列有：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是基于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#,

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

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

 4        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