oracle rowid的理解

 

一. 官網說明

       owid的定義：A globally unique address for a row in a database.

       rowid 分為extended rowid 和 restricted rowid.

1.1 Restricted ROWID

       Internally, the ROWID is a structure that holds information that the database server needs to access a row.The restricted internal ROWID is 6 bytes on most platforms.

Each restricted rowid includes the following data:

       （1）Datafile identifier

       （2）Block identifier

       （3）Row identifier

       The restricted ROWID pseudocolumn is returned to client applications in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the ROWID.

1.2  Extended ROWID

       The extended ROWID datatype includes the data in the restricted rowid plus a data object number. The data object number is an identification number assigned to every database segment. The extended internal ROWID is 10 bytes
on most platforms.

       Data in an extended ROWID pseudocolumn is returned to the client application in the form of an 18-character string (for example, "AAAA8mAALAAAAQkAAA"), which represents a base 64 encoding of the components of the extended ROWID in a four-piece format,
OOOOOOFFFBBBBBBRRR. Extended rowids are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended rowid contents. The package functions extract and provide information that would be available directly from a restricted rowid
as well as information specific to extended rowids.

 

1.3 Rowid Format

       Oracle Database uses a rowid to uniquely identify a row. Internally, the rowid is a structure that holds information that the database needs to access a row. A rowid is not physically stored in the database, but is inferred
from the file and block on which the data is stored.

       An extended rowid includes a data object number.
This rowid type uses a base 64 encoding of the physical address for each row. The encoding characters are A-Z, a-z, 0-9, +, and /.

Example 12-1 queries the ROWID pseudocolumn to show the extended rowid of the row in the employees table for employee 100.
Example 12-1 ROWID Pseudocolumn

SQL> SELECT ROWID FROM employees WHERE employee_id = 100;

ROWID

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

AAAPecAAFAAAABSAAA

Figure 12-8 illustrates the format of an extended rowid.
Figure 12-8 ROWID Format

An extended rowid is displayed in a four-piece format, OOOOOOFFFBBBBBBRRR, with the format divided into the following components:

（1）OOOOOO

       The data object number identifies the segment (data object AAAPec in Example 12-1). A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster, have the same data object number.

（2）FFF

       The tablespace-relative data file number identifies the data file that contains the row (file AAF in Example 12-1).

（3）BBBBBB

       The data block number identifies the block that contains the row (block AAAABS in Example 12-1). Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files
of the same tablespace.

（4）RRR

       The row number identifies the row in the block (row AAA in Example 12-1).

  

       After a rowid is assigned to a row piece, the rowid can change in special circumstances. For example,

（1）if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on.

（2）If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities.

 

二、RowId 說明

      rowid 是偽列，用來唯一的定位某一資料行，rowid在資料庫中不是物理存在的，是行的一個推導屬性，在查詢時根據資料行所在的檔案和資料區塊構造出來的一個偽列。

      rowid是儲存在索引中的一個既定值（當行確定後），rowid是訪問表中一行最快的方式。

 

三、RowId結構

         擴充的rowid格式：

             OOOOOO              FFF                BBBBBB          RRR

             資料對象編號        檔案編號        塊編號           行編號

 

         受限的rowid格式：

             BBBBBBBB            RRRR        FFFF

             資料對象編號         塊編號      檔案編號

     這裡我們主要討論oracle8i以後的擴充的rowid， 擴充的ROWID 在磁碟上需要10 個位元組的儲存空間，並使用18 個字元來顯示。

      它包含下列組成元素：

       1. 資料對象編號：每個資料對象（如表或索引）在建立時都分配有此編號，並且此編號在資料庫中是唯一的

       2. 檔案編號：此編號對於資料表空間中的每個資料檔案是唯一的

       3. 塊編號：表示包含此行的塊在資料檔案中的位置

       4. 行編號：標識塊頭中行目錄位置

       在內部，儲存的10個位元組(bytes)，即80位(bit)又按如下規則進行劃分：

       （1）資料對象編號需要32 bit    （每個資料庫最多有4G個對象)

       （2）相關檔案編號需要10 bit    （每個對象最多有1022個檔案（2個檔案預留））

       （3）塊編號需要22 bit  （每個檔案最多有4M個BLOCK）

       （4）行編號需要16 bit  （每個BLOCK最多有64K個ROW）

      結論：oracle資料庫的對象數和資料檔案數是受rowid結構格式限制的。

      擴充的ROWID 使用以64 為基數的編碼方案來顯示，該方案將六個位置用於資料對象編號、三個位置用於相關檔案編號、六個位置用於塊編號、三個位置用於行編號。 以64 為基數的編碼方案使用字元“A-Z”、“a-z”、“0-9” 和“/”。共有64 個字元，

根據dbms_rowid包，我們還可以得到rowid相關的具體資訊，如下例所示：

SQL> SELECT rowid,  2   dbms_rowid.rowid_object(rowid) obj_id,  3   dbms_rowid.rowid_relative_fno(rowid) datafile#,  4   dbms_rowid.rowid_block_number(rowid) blocknum,  5   dbms_rowid.rowid_row_number(rowid) rowno  6   from td_s_resstate where rownum<5; ROWID                  OBJ_ID  DATAFILE#   BLOCKNUM      ROWNO------------------ ---------- ---------- ---------- ----------AAALB3AA9AAACVkAAa      45175         61       9572         26AAALB3AA9AAACVkAAb      45175         61       9572         27AAALB3AA9AAACVkAAc      45175         61       9572         28AAALB3AA9AAACVkAAd      45175         61       9572         29 

上面例子的第一條記錄rowid：AAALB3AA9AAACVkAAa：

      資料對象編號:AAALB3

      檔案編號：AA9

      塊編號：AAACVk

      行編號：AAa

 

四、RowId的改變與用途：

      對於有ROWID 的行（Oracle 中最常見的行“類型”；除了IOT 中的行之外，所有行都有ROWID）， 以前ROWID 是不可變的。插入一行時，會為之關聯一個ROWID（一個地址），而且這個ROWID 會一直與該行關聯，直到這一行被刪除（被物理地從資料庫刪除）。但是，後來情況發生了變化，因為現在有些操作可能會導致行的ROWID 改變，例如：
              1.在分區表中更新一行的分區鍵，使這一行必須從一個分區移至另一個分區。
              2.使用FLASHBACK TABLE 命令將一個資料庫表恢複到以前的每個時間點。
              3.執行MOVE 操作以及許多分區操作，如分解或合并分區。
              4.使用ALTER TABLE SHRINK SPACE 命令執行段收縮。

              5.對一個表進行了EXP/IMP。

如今，由於ROWID 可能過一段時間會改變（因為它不再是不可變的），所以不建議把它們作為單獨的列物理地儲存在資料庫表中。也就是說，使用ROWID 作為一個資料庫列的資料類型被認為是一種不好的實踐做法。應當避免這種做法，而應使用行的主鍵（這應該是不可變的），另外參考完整性可以確保資料的完整性。對此用ROWID 類型是做不到的，不能用ROWID 建立從子表到一個父表的外鍵，而且不能保證跨表的完整性。你必須使用主鍵約束。

那ROWID 類型有什麼用呢？在允許終端使用者與資料互動的應用中，ROWID 還是有用的。ROWID 作為行的一個物理地址，要訪問任何錶中的某一行，這是最快的方法。如果應用從資料庫讀出資料並將其提供給終端使用者，它試圖更新這一行時就可以使用ROWID。應用這種方式，只需最少的工作就可以更新當前行（例如，不需要索引尋找再次尋找行），並通過驗證行值未被修改來確保這一行與最初讀出的行是同一行。所以，在採用樂觀鎖定的應用中ROWID 還是有用的。

 

五、RowId的常見sql使用：

 1、刪除表中多餘的重複記錄（多個欄位），只留有rowid最小的記錄  
  delete   from   vitae   a  
  where   (a.peopleId,a.seq)   in     (select   peopleId,seq   from   vitae   group   by   peopleId,seq   having   count(*)   >   1)  

  and   rowid   not   in   (select   min(rowid)   from   vitae   group   by   peopleId,seq   having   count(*)>1)   
    
 2、尋找表中多餘的重複記錄（多個欄位），不包含rowid最小的記錄  
  select   *   from   vitae   a  
  where   (a.peopleId,a.seq)   in     (select   peopleId,seq   from   vitae   group   by   peopleId,seq   having   count(*)   >   1)  

  and   rowid   not   in   (select   min(rowid)   from   vitae   group   by   peopleId,seq   having   count(*)>1)

 

附：UROWID（rowid的‘近親’）

UROWID(Universal Rowid)表示一行資料的邏輯地址。一般情況下，索引組織表(IOT)和遠端資料庫(可以是非Oracle資料庫)中的表需要用到UROWID。

UROWID 是行主索引值的一個表示，因此，其大小不定，這取決於它指向的對象。

UROWID 基於行的主鍵產生，所以從某種意義上講，好像它是隨行儲存的，但是事實上並非如此，因為UROWID 並不作為一個單獨的列存在，而只是作為現有列的一個函數。

UROWID的子類型

• 物理的（實際上就是物理ROWID加一個子類型標記位元組）
• 邏輯的（基於主鍵，用於IOT）
• 遠端（其他庫中的表）