Oracle行內鏈接不會引起USER_TABLES中CHAIN_CNT值變化

    前幾天和群里網友討論一個關於行內鏈接（intra-block chaining）的問題，問題非常有意思，恰好今天有空，順便整理了一下這些知識點。

    問題描述：下麵SQL，創建一個超過255列的表（實際為256列），然後插入幾條數據，然後對錶做ANALYZE分析過後，但是發現user_tables的CHAIN_CNT欄位值為0，chained_rows表中沒有記錄，為什麼會這樣？

declare

v_sql varchar2(32767) ;

begin

v_sql := 'create table t_chain1 ( ' ;

for i in 1..256 loop

v_sql := v_sql || 'id'||i||' number,' ;

end loop ;

v_sql := rtrim(v_sql, ',') || ')';

execute immediate v_sql;

end ;

/

insert into t_chain1(id256) values(1);

insert into t_chain1(id256) values(2);

insert into t_chain1(id256) values(3);

commit;

analyze table t_chain1 list chained rows;

analyze table t_chain1 compute statistics;

SQL> select table_name, num_rows, chain_cnt, avg_row_len from user_tables

  2  where table_name='T_CHAIN1';

TABLE_NAME                       NUM_ROWS  CHAIN_CNT AVG_ROW_LEN

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

T_CHAIN1                                3          0         267

SQL> select * from chained_rows;

no rows selected

在分析這個問題前，我們要先瞭解一下Oracle資料庫當中的Row Migration (行遷移) & Row Chaining (行鏈接)概念：

    當表中一行的數據不能在一個數據block中放入的時候，這個時候就會發生兩種情況，一種是行鏈接（Row Chaining），另外一種就是行遷移（Row Migration）了。

   行鏈接產生在第一次插入數據的時候如果一個block不能存放一行記錄的情況下。這種情況下，Oracle將使用鏈接一個或者多個在這個段中保留的block存儲這一行記錄，行鏈接比較容易發生在比較大的行上，例如行上有LONG、LONG RAW、LOB等數據類型的欄位，這種時候行鏈接是不可避免的會產生的。

   當一行記錄初始插入的時候事可以存儲在一個block中的，由於更新操作導致行長增加了，而block的自由空間已經完全滿了，這個時候就產生了行遷移。在這種情況下，Oracle將會遷移整行數據到一個新的block中（假設一個block中可以存儲下整行數據），Oracle會保留被遷移行的原始指針指向新的存放行數據的block，這就意味著被遷移行的ROW ID是不會改變的。

當發生了行遷移或者行鏈接，對這行數據操作的性能就會降低，因為Oracle必須要掃描更多的block來獲得這行的信息

row chain：When a row is too large to fit into any block, row chaining occurs. In this case, the Oracle devide the row into smaller chunks. each chunk is stored in a block along with the necessary poiters to retrive and assemble the entire row.

row migration：when a row is to be updated and it cannot find the necessary free space in its block, the Oracle will move the entire row into a new block and leave a pointer from the orginal block to the new location. This process is called row migration.

那麼現在回到這個問題，我們先來看看表t_chain1的rowid,以及對應的文件號等信息：

select dbms_rowid.rowid_object(rowid)       obj#  ,

       dbms_rowid.rowid_relative_fno(rowid) rfile#,

       dbms_rowid.rowid_block_number(rowid) block#,

       dbms_rowid.rowid_row_number(rowid)   row#

from t_chain1 ;

我們看到這三條記錄對應的行數據在BLOCK中的相對位置為1,3,5，那麼說明當表的欄位個數超過255時，是發生了行內鏈接的，關於這個，我們繼續回顧一下行片段（row pieces）和行內鏈接（intra-block chaining）等概念

Row Format and Size

Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece. However, if all of a row's data cannot be inserted into a single data block or if an update to an existing row causes the row to outgrow its data block, then Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in more than one row piece, it is chained across multiple blocks.

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row's pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

Each row piece, chained or unchained, contains a row header and data for all or some of the row's columns. Individual columns can also span row pieces and, consequently, data blocks. Figure 5-3 shows the format of a row piece:

這裡面介紹了行內鏈接（intra-block chaining）概念，當一個表的列超過255列，ORACLE會把行記錄分成兩個或多個行片段（row piece），一個row piece包含255個欄位,如果表中有312個欄位，那麼就會有三個行片段（row piece）, 行內鏈接（intra-block chaining）只是多個行片段（row piece）通過rowid串聯起來，這也是上面測試案例，你看到的對應rowid返回該行數據在BLOCK中的相對位置對應是1、3、5 ，而不是1、2、3的原因，因為行內鏈接（intra-block chaining）發生的同一個塊內（block），所以它並不會產生額外的IO操作，也就是說不影響IO（當然這個要看你如何理解）。那麼我使用alter system dump 來看看行在塊裡面的信息吧

去$ORACLE_BASE下麵的udmp找到對應的trc文件，我實驗中生成的文件為scm2_ora_20850.trc

cc：表示列數，fb:H是指行記錄的頭，L是指行記錄的最後一列，F是指行記錄的第一列. 實驗結果跟理論是一致的。到這裡似乎一直沒有回到我們的問題來，那麼我們先來看看官方文檔對AVG_ROW_LEN的解釋：

Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID

註意我標記為紅色的部分，顯然AVG_ROW_LEN記錄的是發生了行鏈接或行遷移的行數，要麼是數據從一個block遷移到另外一個block，要麼是數據從一個block鏈接到另外一個block。而行內鏈接（intra-block chaining)是發生在同一個block內的，所以這裡實驗產生的行內鏈接並不會記錄到AVG_ROW_LEN裡面，所以這就解釋了AVG_ROW_LEN為0，chained_rows沒有記錄的原因。

下麵我們來構造一個行鏈接的案例，如下所示，新建表t_chain,使其一行的記錄無法插入到一個block裡面，那麼當插入的時候，就會產生行鏈接，此時對錶做ANALYZE分析過後，但是發現user_tables的CHAIN_CNT欄位值不為0了，chained_rows表中也會有相關記錄

declare

v_sql varchar2(32767) ;

begin

v_sql := 'create table t_chain ( ' ;

for i in 1..256 loop

v_sql := v_sql || 'id'||i||' char(36),' ;

end loop ;

v_sql := rtrim(v_sql, ',') || ')';

execute immediate v_sql;

end ;

/

declare

v_sql varchar2(32767) ;

begin

v_sql := 'insert into t_chain select ' ;

for i in 1..255 loop

v_sql := v_sql || '''it is only test'',' ;

end loop ;

v_sql := v_sql || '''it is only test'' from dual; commit;';

dbms_output.put_line( v_sql); --將生成的腳本執行2次

end ;

/

SQL> analyze table t_chain list chained rows;

Table analyzed.

SQL> analyze table t_chain compute statistics;

Table analyzed.

SQL>  select table_name, num_rows, chain_cnt, avg_row_len from user_tables

  2  where table_name='T_CHAIN' ;

TABLE_NAME                       NUM_ROWS  CHAIN_CNT AVG_ROW_LEN

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

T_CHAIN                                 2          2        9481

SQL> select count(1) from chained_rows;

  COUNT(1)

----------

         2

SQL> select * from chained_rows;

OWNER_NAME   TABLE_NAME   CLUSTER_NAME   PARTITION_NAME  SUBPARTITION_NAME   HEAD_ROWID      ANALYZE_T

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

SYS             T_CHAIN                                       N/A          ACOhqAABAAAVMLAAA 10-JUL-16

SYS             T_CHAIN                                       N/A          AACOhqAABAAAVMNAAA 10-JUL-16

SQL> 

參考資料：

https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#REFRN20286

http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#i4383

http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#CNCPT1129