敗家貓

環境
os:win2k
oracle:10.1.0.2.0

1、先建立一個測試table
create table test (id number);

2、準備觀查 rowid的sql

select id,rowid, dbms_rowid.rowid_object(rowid), dbms_rowid.rowid_block_number(rowid), dbms_rowid.rowid_row_number(rowid), dbms_rowid.rowid_relative_fno(rowid) from test;

簡單說明
rowid_object:第筆資料的對應號碼
rowid_relative_fno:這筆資料在那個檔案，可查相關的view: v$datafile,dba_users
rowid_block_number:這筆資料在那個block中
rowid_row_number:這筆資料是第幾個填入的

3、實驗過程

一開始沒有資料

SQL> select id,rowid,dbms_rowid.rowid_object(rowid) from test;         ID ROWID              DBMS_ROWID.ROWID_OBJECT(ROWID) ---------- ------------------ ------------------------------

增加一筆資料

SQL> insert into test values(1); 1 row inserted

查看rowid的變化 (還未commit)

SQL> select id,rowid,dbms_rowid.rowid_object(rowid),dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from test;         ID ROWID              DBMS_ROWID.ROWID_OBJECT(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_ROW_NUMBER(RO DBMS_ROWID.ROWID_RELATIVE_FNO( ---------- ------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------          1 AAAW8uAABAAAOoCAAA                          93998                          59906                              0                              1

測試進行rollback

SQL> rollback; Rollback complete

再次查看rowid

SQL> select id,rowid,dbms_rowid.rowid_object(rowid),dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from test;         ID ROWID              DBMS_ROWID.ROWID_OBJECT(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_ROW_NUMBER(RO DBMS_ROWID.ROWID_RELATIVE_FNO( ---------- ------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

由上面可得知，當資料進行inert時，就已分配給你一個位置了，如沒有進行完交易的話，會再把原來佔有的位置歸還出來

再接著做新增刪除的影響，先新增幾筆資料(都已commit了)

SQL> select id,rowid,dbms_rowid.rowid_object(rowid),dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from test;         ID ROWID              DBMS_ROWID.ROWID_OBJECT(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_ROW_NUMBER(RO DBMS_ROWID.ROWID_RELATIVE_FNO( ---------- ------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------          2 AAAW8uAABAAAOoCAAA                          93998                          59906                              0                              1          3 AAAW8uAABAAAOoCAAB                          93998                          59906                              1                              1          4 AAAW8uAABAAAOoCAAC                          93998                          59906                              2                              1          5 AAAW8uAABAAAOoCAAD                          93998                          59906                              3                              1          6 AAAW8uAABAAAOoCAAE                          93998                          59906                              4                              1          7 AAAW8uAABAAAOoCAAF                          93998                          59906                              5                              1          8 AAAW8uAABAAAOoCAAG                          93998                          59906                              6                              1          9 AAAW8uAABAAAOoCAAH                          93998                          59906                              7                              1

刪除其中一筆資料

SQL> delete test where id=5;

查看現在的況狀

SQL> select id,rowid,dbms_rowid.rowid_object(rowid),dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from test;         ID ROWID              DBMS_ROWID.ROWID_OBJECT(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_ROW_NUMBER(RO DBMS_ROWID.ROWID_RELATIVE_FNO( ---------- ------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------          2 AAAW8uAABAAAOoCAAA                          93998                          59906                              0                              1          3 AAAW8uAABAAAOoCAAB                          93998                          59906                              1                              1          4 AAAW8uAABAAAOoCAAC                          93998                          59906                              2                              1          6 AAAW8uAABAAAOoCAAE                          93998                          59906                              4                              1          7 AAAW8uAABAAAOoCAAF                          93998                          59906                              5                              1          8 AAAW8uAABAAAOoCAAG                          93998                          59906                              6                              1          9 AAAW8uAABAAAOoCAAH                          93998                          59906                              7                              1

再來接著inert多筆資料

SQL> select id,rowid,dbms_rowid.rowid_object(rowid),dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from test;         ID ROWID              DBMS_ROWID.ROWID_OBJECT(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_ROW_NUMBER(RO DBMS_ROWID.ROWID_RELATIVE_FNO( ---------- ------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------          2 AAAW8uAABAAAOoCAAA                          93998                          59906                              0                              1          3 AAAW8uAABAAAOoCAAB                          93998                          59906                              1                              1          4 AAAW8uAABAAAOoCAAC                          93998                          59906                              2                              1         12 AAAW8uAABAAAOoCAAD                          93998                          59906                              3                              1          6 AAAW8uAABAAAOoCAAE                          93998                          59906                              4                              1          7 AAAW8uAABAAAOoCAAF                          93998                          59906                              5                              1          8 AAAW8uAABAAAOoCAAG                          93998                          59906                              6                              1          9 AAAW8uAABAAAOoCAAH                          93998                          59906                              7                              1         10 AAAW8uAABAAAOoCAAI                          93998                          59906                              8                              1         11 AAAW8uAABAAAOoCAAJ                          93998                          59906                              9                              1         13 AAAW8uAABAAAOoCAAK                          93998                          59906                             10                              1         14 AAAW8uAABAAAOoCAAL                          93998                          59906                             11                              1         15 AAAW8uAABAAAOoCAAM                          93998                          59906                             12                              1         16 AAAW8uAABAAAOoCAAN                          93998                          59906                             13                              1         17 AAAW8uAABAAAOoCAAO                          93998                          59906                             14                              1         18 AAAW8uAABAAAOoCAAP                          93998                          59906                             15                              1         19 AAAW8uAABAAAOoCAAQ                          93998                          59906                             16                              1         20 AAAW8uAABAAAOoCAAR                          93998                          59906                             17                              1

結論
1、可發現當刪除資料後空出來的空間，並不會馬上被利用
2、資料在table中並不會順序填入，這就是為何大的table要建立index的原因(index裏的資料是有排序過的)
3、由dbms_rowid中得到block的數量再乘以db_block_size就可以求出表的大小 (可由user_segments中的bytes)