网站做cdn,杭州设计企业网站高端公司,淄博网站推广优化,宿州做网站在oracle10g和下#xff0c;伪列包括如下内容#xff1a;lHierarchical Query Pseudocolumns分级查询是oracle提供的递归查询语法#xff0c;在这里不做展开。只有在分级查询下#xff0c;才可以使用以下伪列#xff1a;1.CONNECT_BY_ISCYCLE Pseudocolumn2.CONNECT_BY_IS…在oracle10g和下伪列包括如下内容lHierarchical Query Pseudocolumns分级查询是oracle提供的递归查询语法在这里不做展开。只有在分级查询下才可以使用以下伪列1.CONNECT_BY_ISCYCLE Pseudocolumn2.CONNECT_BY_ISLEAF Pseudocolumn3.LEVEL PseudocolumnlSequence PseudocolumnsSequence有以下2个伪列1.CURRVAL返回当前sequence值2.NEXTVAL增加sequence并返回下一个值一般用法sequence.CURRVALsequence.NEXTVALlVersion Query PseudocolumnsVersion Query伪列只有在Flashback Version Query时才有效内容如下1.VERSIONS_STARTSCN and VERSIONS_STARTTIME2.VERSIONS_ENDSCN and VERSIONS_ENDTIME3.VERSIONS_XID4.VERSIONS_OPERATIONlCOLUMN_VALUE PseudocolumnlOBJECT_ID PseudocolumnlOBJECT_VALUE PseudocolumnlORA_ROWSCN PseudocolumnlROWID PseudocolumnlROWNUM PseudocolumnlXMLDATA 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)。SQLselect ora_rowscn,username from t;ORA_ROWSCN USERNAME---------- ------------------------------86516279 SYSTEM86516279 SYS86516279 OUTLN86516279 LINCINQ86516279 TEST86516279 LINC86516279 SPLEXUC86516279 DIP86516279 TSMSYS86516279 WMSYS86516279 DBSNMP11 rows selected.需要查询scn对于的具体时间可以用函数SCN_TO_TIMESTAMPSQLalter session set nls_timestamp_formatyyyy-mm-dd hh24:mi:ss;Session altered.SQLselect scn_to_timestamp(ora_rowscn),username from t;SCN_TO_TIMESTAMP(ORA_ROWSCN)USERNAME------------------------------ ------------------------------2011-06-01 13:13:31SYSTEM2011-06-01 13:13:31SYS2011-06-01 13:13:31OUTLN2011-06-01 13:13:31LINCINQ2011-06-01 13:13:31TEST2011-06-01 13:13:31LINC2011-06-01 13:13:31SPLEXUC2011-06-01 13:13:31DIP2011-06-01 13:13:31TSMSYS2011-06-01 13:13:31WMSYS2011-06-01 13:13:31DBSNMP11 rows selected.ROWIDRowid是基于64位编码的18个字符格式如下data_object_idfile_idblock_numberrow_numberOOOOOOFFFBBBBBBRRRSQLselect rowid,username from t;ROWIDUSERNAME------------------ ------------------------------AAAE0LAAOAABQqMAAA SYSTEMAAAE0LAAOAABQqMAAB SYSAAAE0LAAOAABQqMAAC OUTLNAAAE0LAAOAABQqMAAD LINCINQAAAE0LAAOAABQqMAAE TESTAAAE0LAAOAABQqMAAF LINCAAAE0LAAOAABQqMAAG SPLEXUCAAAE0LAAOAABQqMAAH DIPAAAE0LAAOAABQqMAAI TSMSYSAAAE0LAAOAABQqMAAJ WMSYSAAAE0LAAOAABQqMAAK DBSNMP11 rows selected.通过dbms_rowid包我们可以获得rowid对应的记录详细信息。SQLselect 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: AAAE0LAAOAABQqMAAAold1: select dbms_rowid.rowid_object(1) data_object_id#,new1: select dbms_rowid.rowid_object(AAAE0LAAOAABQqMAAA) data_object_id#,Enter value for 1: AAAE0LAAOAABQqMAAAold2:dbms_rowid.rowid_relative_fno(1) rfile#,new2:dbms_rowid.rowid_relative_fno(AAAE0LAAOAABQqMAAA) rfile#,Enter value for 1: AAAE0LAAOAABQqMAAAold3:dbms_rowid.rowid_block_number(1) block#,new3:dbms_rowid.rowid_block_number(AAAE0LAAOAABQqMAAA) block#,Enter value for 1: AAAE0LAAOAABQqMAAAold4:dbms_rowid.rowid_row_number(1) row# from dualnew4:dbms_rowid.rowid_row_number(AAAE0LAAOAABQqMAAA) row# from dualDATA_OBJECT_ID#RFILE#BLOCK#ROW#--------------- ---------- ---------- ----------19723143303800ROWNUM在对表的查询中每返回一条记录rownum伪列就返回一个数字代表查询返回的行的编号。SQLselect rownum,username from t;ROWNUM USERNAME---------- ------------------------------1 SYSTEM2 SYS3 OUTLN4 LINCINQ5 TEST6 LINC7 SPLEXUC8 DIP9 TSMSYS10 WMSYS11 DBSNMP11 rows selected.从下面的例子可以看出rownum返回的是查询过程中返回记录的顺序并不是查询结果的序列号。SQLselect rownum,username from t order by username;ROWNUM USERNAME---------- ------------------------------11 DBSNMP8 DIP6 LINC4 LINCINQ3 OUTLN7 SPLEXUC2 SYS1 SYSTEM5 TEST9 TSMSYS10 WMSYS11 rows selected.SQLselect rownum,username from (select username from t order by username);ROWNUM USERNAME---------- ------------------------------1 DBSNMP2 DIP3 LINC4 LINCINQ5 OUTLN6 SPLEXUC7 SYS8 SYSTEM9 TEST10 TSMSYS11 WMSYS11 rows selected.
