如何解析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了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。