物理rowid兩種格式：

受限rowid （8i之前）

擴充套件rowid （8i以後）

Extended rowids use a base 64 encoding of the
physical address for each row selected. The
encoding characters are A-Z, a-z, 0-9, +, and /

rowid ：在oracle中稱偽列，不是建立表的時候的某個列。如果要刪除兩行相同的資料，可以運用rowid。
rowid記錄這一行資料在磁碟上的實際儲存的物理位置。desc t和 select * from t均查不出來rowid

SQL> connect scott/tiger;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott

SQL>
SQL>
SQL> select t3.* ,rowid from t3;

ID NAME         ROWID
-- -------------------------------
1    a      AAAM40AAGAAAAAMAAA
2    b      AAAM40AAGAAAAAMAAB
3    c      AAAM40AAGAAAAAMAAC

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

以擴充套件rowid格式為例： AAAM40AAGAAAAAMAAA

rowid一共18位，分為四個部分

AAAM40： 資料物件編號（data object number）：表示的是t2這個表段的編號，在dba_objects這個檢視中，用 data_object_id表示。注意 object_id是物件編號，data_object_id是資料段編號，
它跟物理儲存位置有關。
         The data object number that identifies the database segment (AAAM40 in the example). Schema objects in thesame segment, such as a cluster of tables,have the same data object number.


AAG ：         相對檔案編號（relative file number）
AAAAAM： 包含資料行的資料塊編號（block number）
AAA ：         行編號（ row number）

SQL> select object_id,data_object_id from dba_objects where object_name='T2';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
52657 52786

SQL> alter table t2 rename to t3;

Table altered

SQL>
SQL> select object_id,data_object_id from dba_objects where object_name='T3';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
52657 52786

SQL>

SQL> select TABLESPACE_NAME from user_tables where table_name='T3';

TABLESPACE_NAME
------------------------------
USERS

SQL> alter table t3 move tablespace TT;

Table altered

可以看到，在移動表空間後，資料段發生了變化，因此物件編號也發生了變化，如下：

SQL> select t3.* ,rowid from t3;

ID NAME         ROWID
-- -------------------------------
1    a      AAAM40AAGAAAAAMAAA
2    b      AAAM40AAGAAAAAMAAB
3    c      AAAM40AAGAAAAAMAAC

SQL> select object_id,data_object_id from dba_objects where object_name='T3';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
52657 52787

=====================================================

哪些操作會使資料物件（段）編號發生變化呢？

SQL> truncate table t3;

Table truncated

SQL> select object_id,data_object_id from dba_objects where object_name='T3';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
52657 52788

truncate操作是ddl命令，只修改後設資料（後設資料放在資料字典中，存放在system表空間中），不涉及到修改表資料。
truncate t3 時，只修改system表空間對t3表的描述。實際上，把原來的T3表重新命名，然後重新建立了
一個新的空表T3 ，所以truncate 很快，truncate一個幾百G的表和truncate 幾條資料的表時間差不多。

====================================================

delete 表的某一列資料，oracle到底做了什麼？

先把這一列所佔的空間釋放掉，其次要把後設資料刪除掉，所以在大表上刪除某一列，非常慢。

SQL> show user;
User is "scott"

SQL> select count(*) from t;

COUNT(*)
----------
1611552

SQL> select bytes/1024/1024 M from dba_segments where segment_name='T';

         M
----------
       178

SQL> desc t;
Name           Type          Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER          VARCHAR2(30) Y
OBJECT_NAME    VARCHAR2(128) Y
SUBOBJECT_NAME VARCHAR2(30) Y
OBJECT_ID      NUMBER        Y
DATA_OBJECT_ID NUMBER        Y
OBJECT_TYPE    VARCHAR2(19) Y
CREATED        DATE          Y
LAST_DDL_TIME DATE          Y
TIMESTAMP      VARCHAR2(19) Y
STATUS         VARCHAR2(7)   Y
TEMPORARY      VARCHAR2(1)   Y
GENERATED      VARCHAR2(1)   Y
SECONDARY      VARCHAR2(1)   Y

SQL> execute dbms_stats.gather_table_stats('SCOTT','T');

SQL> alter table t drop column OBJECT_TYPE;

Table altered

SQL>
SQL>
SQL> execute dbms_stats.gather_table_stats('SCOTT','T');

PL/SQL procedure successfully completed

SQL> select num_rows,blocks from user_tables where table_name='T';

NUM_ROWS BLOCKS
---------- ----------
3225195 44287

SQL>

可以看到blocks沒有發生變化，但是num_rows發生了變化（有可能比之前的值更大）

=========================

大表刪除列

1：刪除列，但不釋放空間

SQL> desc t;
Name           Type        Nullable Default Comments
-------------- ----------- -------- ------- --------
OBJECT_ID      NUMBER      Y
DATA_OBJECT_ID NUMBER      Y
CREATED        DATE        Y
LAST_DDL_TIME DATE        Y
STATUS         VARCHAR2(7) Y
TEMPORARY      VARCHAR2(1) Y
GENERATED      VARCHAR2(1) Y
SECONDARY      VARCHAR2(1) Y

SQL>
SQL>
SQL> alter table t set unused column status;

oracle執行這句話，不釋放空間，它只重新命名了列，可以從select * from dba_tab_cols where wner='SCOTT' 驗證

SQL> desc t;
Name           Type        Nullable Default Comments
-------------- ----------- -------- ------- --------
OBJECT_ID      NUMBER      Y
DATA_OBJECT_ID NUMBER      Y
CREATED        DATE        Y
LAST_DDL_TIME DATE        Y
TEMPORARY      VARCHAR2(1) Y
GENERATED      VARCHAR2(1) Y
SECONDARY      VARCHAR2(1) Y

2：使用這個命令以後，就將原來的status列刪除掉了。（釋放了空間）

SQL> alter table t drop unused columns;

Table altered

然後用 select * from dba_tab_cols where wner='SCOTT' 可以驗證

========

3：將表空間offline後，可以刪除表中的資料（為什麼？）

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS
EMPLOYEES                      USERS
T                              USERS
PLCH_COMMISSIONS               USERS
PLCH_SALARIES                  USERS
T3                             TT

9 rows selected

SQL> alter tablespace USERS offline;

Tablespace altered

SQL> select count(*) from t;

select count(*) from t

ORA-00376: 此時無法讀取檔案 4
ORA-01110: 資料檔案 4: 'C:\ORADATA\ORCL\USERS01.DBF'

SQL>

SQL> desc t;
Name           Type        Nullable Default Comments
-------------- ----------- -------- ------- --------
OBJECT_ID      NUMBER      Y
DATA_OBJECT_ID NUMBER      Y OBJECT_ID
CREATED        DATE        Y
LAST_DDL_TIME DATE        Y
TEMPORARY      VARCHAR2(1) Y
GENERATED      VARCHAR2(1) Y
SECONDARY      VARCHAR2(1) Y

SQL>

SQL> drop table t;

Table dropped

SQL>
SQL> flashback table t to before drop;

Done

SQL> alter tablespace users online;

Tablespace altered

SQL> select count(*) from t;

COUNT(*)
----------
3222976

將表空間offline以後，刪除表是ddl操作，操作的是後設資料，它存放在system表空間中。

====================================================================

AAG ：   相對檔案編號（relative file number），用dba_data_files 中的relative_fno欄位表示。
         一個database最多包含65536個資料檔案，一個表空間最多包含1023個資料檔案。
         因為G 轉換為十進位制就是6，那麼AAG就表示6號資料檔案

SQL> select file_name,relative_fno from dba_data_files where tablespace_name='TT';

FILE_NAME RELATIVE_FNO
-------------------------------------------------
C:\ORADATA\ORCL\TT.DBF 6

AAAAAM ：資料塊編號（block number），M轉換為十進位制是12

可以看到

SQL> select t3.* ,rowid from t3;

ID NAME ROWID
-------------------------
1 a AAAM40AAGAAAAAMAAA

(1,'a')這條資料存放在6號資料檔案的第12號塊上

AAA ：   行編號（ row number）

         可以看到該資料檔案塊大小為8k ，一個資料塊可以存放很多行資料，它表示在塊上的第幾條資料。
         AAA轉換後是0，0 表示在這個塊上的第1條資料

SQL> show parameter db_block_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

rowid是64進位制，編碼格式

A~Z   0~25
a~z   26~51
0~9   52~61
+     62
/     63

SQL> select t3.* ,rowid from t3;

ID NAME ROWID
-------------------------
1 a AAAM40AAGAAAAAMAAA

AAAM40 AAG AAAAAM AAA

透過dba_objects 檢視，檢視到data_object_id 為52788 ，那麼現在看下64進位制數和十進位制如何轉換的，

AAAM40 對應的十進位制編碼為
A A A M 4 0
0 0 0 12 56 52

SQL> select 12*64*64+56*64+52 from dual;

12*64*64+56*64+52
-----------------
52788

其實oracle也提供了一些包，計算rowid

包 dbms_rowid 裡面含有很多函式

SQL> desc dbms_rowid;
Element                Type
---------------------- ---------
ROWID_TYPE_RESTRICTED CONSTANT
ROWID_TYPE_EXTENDED    CONSTANT
ROWID_IS_VALID         CONSTANT
ROWID_IS_INVALID       CONSTANT
ROWID_OBJECT_UNDEFINED CONSTANT
ROWID_CONVERT_INTERNAL CONSTANT
ROWID_CONVERT_EXTERNAL CONSTANT
ROWID_INVALID          EXCEPTION
ROWID_BAD_BLOCK        EXCEPTION
ROWID_CREATE           FUNCTION
ROWID_INFO             PROCEDURE
ROWID_TYPE             FUNCTION
ROWID_OBJECT           FUNCTION
ROWID_RELATIVE_FNO     FUNCTION
ROWID_BLOCK_NUMBER     FUNCTION
ROWID_ROW_NUMBER       FUNCTION
ROWID_TO_ABSOLUTE_FNO FUNCTION
ROWID_TO_EXTENDED      FUNCTION
ROWID_TO_RESTRICTED    FUNCTION
ROWID_VERIFY           FUNCTION

SQL>

SQL> select t3.*,rowid from t3;

ID NAME            ROWID
----------------------------------
1 a         AAAM40AAGAAAAAMAAA
2 b         AAAM40AAGAAAAAMAAB
3 c         AAAM40AAGAAAAAMAAC

SQL>
SQL> select dbms_rowid.rowid_object(rowid) obj, dbms_rowid.rowid_relative_fno(rowid) fno,
2 dbms_rowid.rowid_block_number(rowid) bno, dbms_rowid.rowid_row_number(rowid) rno
3 from t3;

       OBJ        FNO        BNO        RNO
---------- ---------- ---------- ----------
     52788          6         12          0
     52788          6         12          1
     52788          6         12          2

也可以透過dbms_rowid這個包來構造rowid 。

如下就構造了t3表的第一行資料的rowid資訊

SQL> select dbms_rowid.rowid_create(1,52788,6,12,0) from dual;

DBMS_ROWID.ROWID_CREATE(1,5278
------------------------------
AAAM40AAGAAAAAMAAA

其中1表示rowid的兩種型別（0表示受限制，1表示擴充套件）

rowid一點總結

相關文章