如何解析Oracle中的ROWNUM与ROWID

这期内容当中小编将会给大家带来有关如何解析Oracle中的ROWNUM与ROWID，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

一、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指定为该行某列的值。

二、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

                                  大文件表空间的rowid与小文件表空间的结构有所不同，因此要得到正确rowid，需要增加一个新的参数ts_type_in，如：

                 SQL> SELECT DBMS_ROWID.rowid_block_number (ROWID, 'BIGFILE') FROM foo;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID,'BIGFILE')
----------------------------------------------
                                            24

                           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

上述就是小编为大家分享的如何解析Oracle中的ROWNUM与ROWID了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。