如何解析Oracle中的ROWNUM與ROWID

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)如何解析Oracle中的ROWNUM與ROWID，文章內(nèi)容豐富且以專(zhuān)業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

一、ROWNUM

       定義：ROWNUM是一個(gè)偽列，標(biāo)識(shí)了select從一個(gè)表或一組連接(JOIN)的表中查詢(xún)數(shù)據(jù)時(shí)，返回記錄的順序。

              Oracle在執(zhí)行select查詢(xún)時(shí)，會(huì)按照返回的row的順序，依次為row分配一個(gè)序號(hào)：

              返回的第一條row的序號(hào)為1，第二條row的序號(hào)為2，以此類(lèi)推。

              這個(gè)序號(hào)即為每條row的rownum。

       注意1：同一個(gè)查詢(xún)語(yǔ)句中，如果ROWNUM后，含有ORDER BY子句時(shí)：

              Oracle先返回未應(yīng)用ORDER BY處理的結(jié)果，分配ROWNUM后，再根據(jù)ORDER BY子句的要求排序。

              因此，返回的結(jié)果中，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

              未應(yīng)用ORDER BY的結(jié)果為：

                     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連續(xù)，可將ORDER BY放入子查詢(xún)，如：

                     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：對(duì)ROWNUM應(yīng)用>,>=,=,between...and條件，返回結(jié)果都為空：

              因?yàn)?，?dāng)返回第一條記錄，作為結(jié)果的第1條，分配rownum=1，

              應(yīng)用>,>=,=,between...and條件判斷時(shí)，不滿(mǎn)足條件，記錄刪除；

              返回下一條記錄時(shí)，仍作為結(jié)果的第1條，分配rownum=1，仍不滿(mǎn)足條件，依次類(lèi)推，結(jié)果為空。

              select rownum from test where rownum=1;//返回1條記錄（結(jié)果集中的第一條）

              select rownum from test where rownum=2;

              //返回0條。根據(jù)ROWNUM定義，不難看出，返回第1條記錄時(shí)，因ROWNUM=1，因此過(guò)濾掉。

              select rownum from test where rownum>10;

              select rownum from test where rownum between 2 and 4;

       應(yīng)用：

              1）可通過(guò)ROWNUM限制返回結(jié)果的記錄數(shù)（行數(shù)）

                     SQL> select rownum from test where rownum<10;

              2）通過(guò)ROWNUM為表中某列產(chǎn)生一個(gè)唯一（UNIQUE）值

                     SQL> UPDATE table_name SET column_name = ROWNUM;

                     //將rownum指定為該行某列的值。

二、ROWID

       (一)、概念：行的ROWID標(biāo)識(shí)了該行數(shù)據(jù)的地址，ROWID包含如下信息：

                                   數(shù)據(jù)對(duì)象編號(hào)、

                                   該行數(shù)據(jù)，在數(shù)據(jù)文件中的塊編號(hào)

                                   該行在數(shù)據(jù)塊中的位置（第一行為0）

                                   保存該行數(shù)據(jù)的數(shù)據(jù)文件的編號(hào)（第一個(gè)數(shù)據(jù)文件為1）

                     ROWID在插入數(shù)據(jù)時(shí)創(chuàng)建，在刪除數(shù)據(jù)時(shí)，刪除。

                     不能手動(dòng)設(shè)置或刪除ROWID，ORACLE內(nèi)部通過(guò)ROWID組建索引；

       (二)、類(lèi)型

              1、物理ROWID：保存普通表（不含索引組織的表IOT）、聚合表（clustered table）、分區(qū)和子分區(qū)表、索引、分區(qū)和子分區(qū)索引中行的地址；

              2、邏輯ROWID：保存索引組織表（IOT）的地址；

       (三)、物理ROWID

              1、類(lèi)型：物理ROWID包含擴(kuò)展ROWID與受限ROWID兩類(lèi)

                     1)、擴(kuò)展ROWID(extended rowid)

                            支持表空間關(guān)聯(lián)的數(shù)據(jù)塊地址，有效標(biāo)識(shí)分區(qū)表、分區(qū)索引以及普通表和索引中的行。

                            Oracle 8i及更高版本支持extended rowid。

                     2)、受限ROWID(restricted rowid)

                            為了向后兼容，如oracle 7及更低版本。

              2、擴(kuò)展ROWID(extended rowid)

                     a.擴(kuò)展ROWID使用base64為每一行數(shù)據(jù)的物理地址進(jìn)行編碼，包含A-Z,a-z,0-9，+以及/。如下查詢(xún)：

                     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.擴(kuò)展ROWID格式

                            擴(kuò)展ROWID共18位，包含4部分，OOOOOOFFFBBBBBBRRR

                                   a)000000：數(shù)據(jù)對(duì)象編號(hào)，標(biāo)識(shí)了數(shù)據(jù)庫(kù)中的段；

                                   b)FFF：表空間相關(guān)的數(shù)據(jù)文件編號(hào)；

                                   c)BBBBBB：數(shù)據(jù)文件中的數(shù)據(jù)塊編號(hào)；

                                   d)RRR：在數(shù)據(jù)塊中的行編號(hào)；

                     c.DBMS_ROWID包

                            a)dbms_rowid.rowid_object通過(guò)ROWID，獲取該數(shù)據(jù)的對(duì)象編號(hào)

                                   SQL> select dbms_rowid.rowid_object('AAAMfKAAEAAAAAQAAC') as object_id from dual;

                                    OBJECT_ID

                                   ----------------

                                          51146

                            b)dbms_rowid.rowid_relative_fno通過(guò)ROWID獲取數(shù)據(jù)文件編號(hào)

                                   SQL> select dbms_rowid.rowid_relative_fno('AAAMfKAAEAAAAAQAAC') as file_no from dual;

                                   FILE_NO

                                   -------------

                                          4

                            c)dbms_rowid.rowid_block_number通過(guò)ROWID，獲取該數(shù)據(jù)的數(shù)據(jù)塊編號(hào)

                                   SQL> select dbms_rowid.rowid_block_number('AAAMfKAAEAAAAAQAAC') as block_number from dual;

                                   BLOCK_NUMBER

                                   -------------------------

                                          16

                                  大文件表空間的rowid與小文件表空間的結(jié)構(gòu)有所不同，因此要得到正確rowid，需要增加一個(gè)新的參數(shù)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通過(guò)ROWID，獲取數(shù)據(jù)塊中的行編號(hào)

                                   SQL> select dbms_rowid.rowid_row_number('AAAMfKAAEAAAAAQAAC') as row_no from dual;

                                   ROW_NO

                                   ----------

                                          2

              3、受限ROWID(resticted rowid)

                     a.受限r(nóng)owid用二進(jìn)制表示每行數(shù)據(jù)的物理地址，當(dāng)使用SQL Plus查詢(xún)時(shí)，二進(jìn)制表示法被轉(zhuǎn)換為varchar2或16進(jìn)制表示。

                             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.受限r(nóng)owid格式：

                            共16位，包含3部分：AAAAAAAA.BBBB.CCCC

                                   a)AAAAAAAA：保存該行數(shù)據(jù)的數(shù)據(jù)塊編號(hào)

                                   b)BBBB：該行數(shù)據(jù)在數(shù)據(jù)塊中的行編號(hào)

                                   c)CCCC：包含該行數(shù)據(jù)的數(shù)據(jù)文件編號(hào)

       (四)、邏輯ROWID(logical rowid)

              1、概述：索引組織的表(IOT)中，row保存在索引的葉子節(jié)點(diǎn)，可以在塊內(nèi)或塊間移動(dòng)。

              因此，這些rows沒(méi)有固定的物理地址，無(wú)法根據(jù)物理地址來(lái)唯一標(biāo)識(shí)。

              Oracle提供了邏輯ROWID，來(lái)標(biāo)識(shí)IOT中的行，邏輯ROWID是基于表的主鍵；

              Oracle可根據(jù)這些邏輯ROWID為IOT創(chuàng)建第二索引。

              每個(gè)第二索引使用的邏輯ROWID都包含一個(gè)physical guess；

              physical guess標(biāo)識(shí)了當(dāng)創(chuàng)建第二索引時(shí)，IOT中每個(gè)row的塊位置；

三、ROWID相關(guān)的包，DBMS_ROWID

上述就是小編為大家分享的如何解析Oracle中的ROWNUM與ROWID了，如果剛好有類(lèi)似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。