dual,rowid,rownum

一、

dual是一个虚拟表,用来构成select的语法规则,oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情,如下:

  1、查看当前用户,可以在 SQL Plus中执行下面语句 select user from dual;

  2、用来调用系统函数

  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间

  select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名

  select SYS_CONTEXT('USERENV','language') from dual;--获得当前 locale

  select dbms_random.random from dual;--获得一个随机数

  3、得到序列的下一个值或当前值,用下面语句

  select your_sequence.nextval from dual;--获得序列your_sequence的下一个值

  select your_sequence.currval from dual;--获得序列your_sequence的当前值

  4、可以用做计算器 select 7*9 from dual;

  ------

  Oracle系统中dual表是一个“神秘”的表,网上有很多网友都对该表进行了测试,该表只有一行一列,其实该表和系统中的其他表一样,一样可以执行插入、更新、删除操作,还可以执行drop操作。但是不要去执行drop表的操作,否则会使系统不能用,数据库起不了,会报Database startup crashes with ORA-1092错误。此时也不要慌乱,可以通过执行以下步骤来进行恢复。可以用sys用户登陆。

  SQL> create pfile=’d:pfile.bak’ from spfile

  SQL> shutdown immediate

  在d:pfile.bak文件中最后加入一条:

  replication_dependency_tracking = FALSE

  重新启动数据库:

  SQL> startup pfile=’d:pfile.bak’

  SQL> create table “sys”.”DUAL”

  [an error occurred while processing this directive]

二、

ROWID
       (一)、概念:行的ROWID标识了该行数据的地址,ROWID包含如下信息:
                                   数据对象编号、
                                   该行数据,在数据文件中的块编号
                                   该行在数据块中的位置(第一行为0)
                                   保存该行数据的数据文件的编号(第一个数据文件为1)
                     ROWID在插入数据时创建,在删除数据时,删除。
                     不能手动设置或删除ROWID,ORACLE内部通过ROWID组建索引;
       (二)、类型
              1、物理ROWID:保存普通表(不含索引组织的表IOT)、聚合表(clustered table)、分区和子分区表、索引、分区和子分区索引中行的地址;
                
              2、逻辑ROWID:保存索引组织表(IOT)的地址;
       (三)、物理ROWID
              1、类型:物理ROWID包含扩展ROWID与受限ROWID两类
                     1)、扩展ROWID(extended rowid)
                            支持表空间关联的数据块地址,有效标识分区表、分区索引以及普通表和索引中的行。
                            Oracle 8i及更高版本支持extended rowid。
                
                     2)、受限ROWID(restricted rowid)
                            为了向后兼容,如oracle 7及更低版本。
              2、扩展ROWID(extended rowid)
                     a.扩展ROWID使用base64为每一行数据的物理地址进行编码,包含A-Z,a-z,0-9,+以及/。如下查询:
                     SQL> select rowid,dept.* from dept;
                     ROWID                                 DEPTNO  DNAME              LOC
                     -------------------------------- ------------ -------------------- ----------------
                     AAAMfKAAEAAAAAQAAA             10              ACCOUNTING NEW YORK
                     AAAMfKAAEAAAAAQAAB     20         RESEARCH      DALLAS
                     AAAMfKAAEAAAAAQAAC     30              SALES               CHICAGO
                     AAAMfKAAEAAAAAQAAD     40              OPERATIONS  BOSTON
                        
                     b.扩展ROWID格式
                            扩展ROWID共18位,包含4部分,OOOOOOFFFBBBBBBRRR
                                   a)000000:数据对象编号,标识了数据库中的段;
                                   b)FFF:表空间相关的数据文件编号;
                                   c)BBBBBB:数据文件中的数据块编号;
                                   d)RRR:在数据块中的行编号;
                     c.DBMS_ROWID包
                            a)dbms_rowid.rowid_object通过ROWID,获取该数据的对象编号
                                   SQL> select dbms_rowid.rowid_object('AAAMfKAAEAAAAAQAAC') as object_id from dual;
                                   OBJECT_ID
                                   ----------------
                                          51146
                            b)dbms_rowid.rowid_relative_fno通过ROWID获取数据文件编号
                                   SQL> select dbms_rowid.rowid_relative_fno('AAAMfKAAEAAAAAQAAC') as file_no from dual;
                                   FILE_NO
                                   -------------
                                          4
                            c)dbms_rowid.rowid_block_number通过ROWID,获取该数据的数据块编号
                                   SQL> select dbms_rowid.rowid_block_number('AAAMfKAAEAAAAAQAAC') as block_number from dual;
                                   BLOCK_NUMBER
                                   -------------------------
                                          16
                           d)dbms_rowid.rowid_row_number通过ROWID,获取数据块中的行编号
                                   SQL> select dbms_rowid.rowid_row_number('AAAMfKAAEAAAAAQAAC') as row_no from dual;
                                   ROW_NO
                                   ----------
                                          2


              3、受限ROWID(resticted rowid)
                     a.受限rowid用二进制表示每行数据的物理地址,当使用SQL Plus查询时,二进制表示法被转换为varchar2或16进制表示。
                             SQL> select dbms_rowid.rowid_to_restricted(rowid,1) as restricted_rowid,dept.* from scott.dept;
                            RESTRICTED_ROWID  DEPTNO   DNAME                  LOC
                            -------------------------------  ------------- --------------------- ------------------
                            00000010.0000.0004     10                  ACCOUNTING    NEW YORK
                            00000010.0001.0004     20                  RESEARCH         DALLAS
                            00000010.0002.0004     30                  SALES                   CHICAGO
                            00000010.0003.0004     40                  OPERATIONS      BOSTON

                     b.受限rowid格式:
                            共16位,包含3部分:AAAAAAAA.BBBB.CCCC
                                   a)AAAAAAAA:保存该行数据的数据块编号
                                   b)BBBB:该行数据在数据块中的行编号
                                   c)CCCC:包含该行数据的数据文件编号        
                        


       (四)、逻辑ROWID(logical rowid)
              1、概述:索引组织的表(IOT)中,row保存在索引的叶子节点,可以在块内或块间移动。
              因此,这些rows没有固定的物理地址,无法根据物理地址来唯一标识。
              Oracle提供了逻辑ROWID,来标识IOT中的行,逻辑ROWID是基于表的主键;
              Oracle可根据这些逻辑ROWID为IOT创建第二索引。
              每个第二索引使用的逻辑ROWID都包含一个physical guess;
              physical guess标识了当创建第二索引时,IOT中每个row的块位置;


三、ROWID相关的包,DBMS_ROWID
SubprogramDescriptionROWID_BLOCK_NUMBER 
Returns the block number of a ROWID
ROWID_CREATE 
Creates a ROWID, for testing only
ROWID_INFO (过程)
Returns the type and components of a ROWID
ROWID_OBJECT 
Returns the object number of the extended ROWID
ROWID_RELATIVE_FNO 
Returns the file number of a ROWID
ROWID_ROW_NUMBER 
Returns the row number
ROWID_TO_ABSOLUTE_FNO 
Returns the absolute file number associated with the ROWID for a row in a specific table
ROWID_TO_EXTENDED 
Converts a ROWID from restricted format to extended
ROWID_TO_RESTRICTED 
Converts an extended ROWID to restricted format
ROWID_TYPE Function
Returns the ROWID type: 0 is restricted, 1 is extended
ROWID_VERIFY 
Checks if a ROWID can be correctly extended by the ROWID_TO_EXTENDED function

三、

ROWNUM
       定义:        ROWNUM是一个伪列,标识了select从一个表或一组连接(JOIN)的表中查询数据时,返回记录的顺序。
              Oracle在执行select查询时,会按照返回的row的顺序,依次为row分配一个序号:
              返回的第一条row的序号为1,第二条row的序号为2,以此类推。
              这个序号即为每条row的rownum。
       注意1:同一个查询语句中,如果ROWNUM后,含有ORDER BY子句时:
              Oracle先返回未应用ORDER BY处理的结果,分配ROWNUM后,再根据ORDER BY子句的要求排序。
              因此,返回的结果中,ROWNUM顺序是混乱的。如:
                     SQL> SELECT ROWNUM,DEPT.* FROM SCOTT.DEPT ORDER BY DNAME;
                      ROWNUM DEPTNO  DNAME              LOC
                     -------------- ------------- -------------------- -------------
                          1                     10                 ACCOUNTING  NEW YORK
                          4                     40                 OPERATIONS   BOSTON
                          2                     20                 RESEARCH       DALLAS
                          3                     30                 SALES                CHICAGO

              未应用ORDER BY的结果为:
                     SQL> SELECT ROWNUM,DEPT.* FROM SCOTT.DEPT;
                     ROWNUM DEPTNO  DNAME                      LOC
                     -------------- ------------- ---------------------- -------------

                                  1                     10                 ACCOUNTING     NEW YORK
                                  2                     20                 RESEARCH               DALLAS
                                  3                     30                 SALES                          CHICAGO
                                  4                     40                 OPERATIONS             BOSTON
              欲使ORDER BY后的ROWNUM连续,可将ORDER BY放入子查询,如:
                     SQL> SELECT ROWNUM,T.* FROM (SELECT DEPT.* FROM SCOTT.DEPT ORDER BY LOC) T;
                     ROWNUM DEPTNO         DNAME                  LOC
                     -------------- ------------- --------------------- -------------
                                  1                     40                 OPERATIONS       BOSTON
                                  2                     30                 SALES                          CHICAGO
                                  3                     20                 RESEARCH          DALLAS
                                  4                     10                 ACCOUNTING      NEW YORK


       注意2:对ROWNUM应用>,>=,=,between...and条件,返回结果都为空:
              因为,当返回第一条记录,作为结果的第1条,分配rownum=1,
              应用>,>=,=,between...and条件判断时,不满足条件,记录删除;
              返回下一条记录时,仍作为结果的第1条,分配rownum=1,仍不满足条件,依次类推,结果为空。
              select rownum from test where rownum=1;                        //返回1条记录(结果集中的第一条)
              select rownum from test where rownum=2;                        
              //返回0条。根据ROWNUM定义,不难看出,返回第1条记录时,因ROWNUM=1,因此过滤掉。
              select rownum from test where rownum>10;
              select rownum from test where rownum between 2 and 4;
       应用:
              1)可通过ROWNUM限制返回结果的记录数(行数)
                     SQL> select rownum from test where rownum<10;
              2)通过ROWNUM为表中某列产生一个唯一(UNIQUE)值
                     SQL> UPDATE table_name SET column_name = ROWNUM;
                     //将rownum指定为该行某列的值。

posted @ 2016-06-14 18:17  凌零聆  阅读(309)  评论(0编辑  收藏  举报