在oracle10g和下,偽列包括如下內容:
lHierarchical Query Pseudocolumns
分級查詢是oracle提供的遞歸查詢語法,在這里不做展開。只有在分級查詢下,才可以使用以下偽列:
1.CONNECT_BY_ISCYCLE Pseudocolumn
2.CONNECT_BY_ISLEAF Pseudocolumn
3.LEVEL Pseudocolumn
lSequence Pseudocolumns
Sequence有以下2個偽列:
1.CURRVAL:返回當前sequence值
2.NEXTVAL:增加sequence并返回下一個值
一般用法:
sequence.CURRVAL
sequence.NEXTVAL
lVersion 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
lCOLUMN_VALUE Pseudocolumn
lOBJECT_ID Pseudocolumn
lOBJECT_VALUE Pseudocolumn
lORA_ROWSCN Pseudocolumn
lROWID Pseudocolumn
lROWNUM Pseudocolumn
lXMLDATA 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:31SYSTEM
2011-06-01 13:13:31SYS
2011-06-01 13:13:31OUTLN
2011-06-01 13:13:31LINCINQ
2011-06-01 13:13:31TEST
2011-06-01 13:13:31LINC
2011-06-01 13:13:31SPLEXUC
2011-06-01 13:13:31DIP
2011-06-01 13:13:31TSMSYS
2011-06-01 13:13:31WMSYS
2011-06-01 13:13:31DBSNMP
11 rows selected.
ROWID
Rowid是基于64位編碼的18個字符,格式如下:
data_object_idfile_idblock_numberrow_number
OOOOOOFFFBBBBBBRRR
SQL>select rowid,username from t;
ROWIDUSERNAME
------------------ ------------------------------
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#,
2dbms_rowid.rowid_relative_fno('&1') rfile#,
3dbms_rowid.rowid_block_number('&1') block#,
4dbms_rowid.rowid_row_number('&1') row# from dual;
Enter value for 1: AAAE0LAAOAABQqMAAA
old1: select dbms_rowid.rowid_object('&1') data_object_id#,
new1: select dbms_rowid.rowid_object('AAAE0LAAOAABQqMAAA') data_object_id#,
Enter value for 1: AAAE0LAAOAABQqMAAA
old2:dbms_rowid.rowid_relative_fno('&1') rfile#,
new2:dbms_rowid.rowid_relative_fno('AAAE0LAAOAABQqMAAA') rfile#,
Enter value for 1: AAAE0LAAOAABQqMAAA
old3:dbms_rowid.rowid_block_number('&1') block#,
new3:dbms_rowid.rowid_block_number('AAAE0LAAOAABQqMAAA') block#,
Enter value for 1: AAAE0LAAOAABQqMAAA
old4:dbms_rowid.rowid_row_number('&1') row# from dual
new4:dbms_rowid.rowid_row_number('AAAE0LAAOAABQqMAAA') row# from dual
DATA_OBJECT_ID#RFILE#BLOCK#ROW#
--------------- ---------- ---------- ----------
19723143303800
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.