行的儲存（塊內連線與塊外連線）

都說ORACLE一個行能儲存1000個欄位，本人在看CONCEPTS的時候發現這麼一段：
Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces.

咋一眼看上去，以為CONCEPTS說只能儲存256個欄位。 再仔細看：

[@more@]

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 an update to an existing row causes the row
to outgrow its data block, 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/Operformance, because no extra I/O operation is required to retrieve the rest of the row.
大致翻譯一下：
資料庫表的第一行，可以由一個或一個以上的包含小於256個列的行片組成；如果一行記錄可以被記錄在單個資料塊中，那麼一行只有一個行片;
然而，如果一個行的所有資料不能被記錄在單個資料塊中，或一個更新導致了資料增長需要更多的塊來記錄，ORACLE會用多個行片來儲存”行“;
一個資料塊中，一個行經常只包含一個行片;
當ORACLE必須用多個行片來儲存一個行時，就產生了行連線;

當一個表有多於255個欄位時，第255個欄位後面的資料就被連線在同一個資料塊中。這就是所謂的塊內連線。
在塊內連線的情況中，使用者可以在同一個資料塊中得到一行的所有資料，這種情況將不會對效能產生影響，因為這裡沒有多餘的I/O產生。

我們下面來看一下這兩種情況：

1.塊內連線
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
SQL>create table heyf_t1 (col1 varchar2(2), col2 varchar2(2),
col3 varchar2(2),col4 varchar2(2),col5 varchar2(2),col6 varchar2(2),
col7 varchar2(2),col8 varchar2(2),col9 varchar2(2),col10 varchar2(2),
col11 varchar2(2),col12 varchar2(2),col13 varchar2(2),col14 varchar2(2),
col15 varchar2(2),col16 varchar2(2),col17 varchar2(2),col18 varchar2(2),
col19 varchar2(2),col20 varchar2(2),col21 varchar2(2),col22 varchar2(2),
col23 varchar2(2),col24 varchar2(2),col25 varchar2(2),col26 varchar2(2),
col27 varchar2(2),col28 varchar2(2),col29 varchar2(2),col30 varchar2(2),
col31 varchar2(2),col32 varchar2(2),col33 varchar2(2),col34 varchar2(2),
col35 varchar2(2),col36 varchar2(2),col37 varchar2(2),col38 varchar2(2),
col39 varchar2(2),col40 varchar2(2),col41 varchar2(2),col42 varchar2(2),
col43 varchar2(2),col44 varchar2(2),col45 varchar2(2),col46 varchar2(2),
col47 varchar2(2),col48 varchar2(2),col49 varchar2(2),col50 varchar2(2),
col51 varchar2(2),col52 varchar2(2),col53 varchar2(2),col54 varchar2(2),
col55 varchar2(2),col56 varchar2(2),col57 varchar2(2),col58 varchar2(2),
col59 varchar2(2),col60 varchar2(2),col61 varchar2(2),col62 varchar2(2),
col63 varchar2(2),col64 varchar2(2),col65 varchar2(2),col66 varchar2(2),
col67 varchar2(2),col68 varchar2(2),col69 varchar2(2),col70 varchar2(2),
col71 varchar2(2),col72 varchar2(2),col73 varchar2(2),col74 varchar2(2),
col75 varchar2(2),col76 varchar2(2),col77 varchar2(2),col78 varchar2(2),
col79 varchar2(2),col80 varchar2(2),col81 varchar2(2),col82 varchar2(2),
col83 varchar2(2),col84 varchar2(2),col85 varchar2(2),col86 varchar2(2),
col87 varchar2(2),col88 varchar2(2),col89 varchar2(2),col90 varchar2(2),
col91 varchar2(2),col92 varchar2(2),col93 varchar2(2),col94 varchar2(2),
col95 varchar2(2),col96 varchar2(2),col97 varchar2(2),col98 varchar2(2),
col99 varchar2(2),col100 varchar2(2),col101 varchar2(2),col102 varchar2(2),
col103 varchar2(2),col104 varchar2(2),col105 varchar2(2),col106 varchar2(2),
col107 varchar2(2),col108 varchar2(2),col109 varchar2(2),col110 varchar2(2),
col111 varchar2(2),col112 varchar2(2),col113 varchar2(2),col114 varchar2(2),
col115 varchar2(2),col116 varchar2(2),col117 varchar2(2),col118 varchar2(2),
col119 varchar2(2),col120 varchar2(2),col121 varchar2(2),col122 varchar2(2),
col123 varchar2(2),col124 varchar2(2),col125 varchar2(2),col126 varchar2(2),
col127 varchar2(2),col128 varchar2(2),col129 varchar2(2),col130 varchar2(2),
col131 varchar2(2),col132 varchar2(2),col133 varchar2(2),
col134 varchar2(2),col135 varchar2(2),col136 varchar2(2),col137 varchar2(2),
col138 varchar2(2),col139 varchar2(2),col140 varchar2(2),col141 varchar2(2),
col142 varchar2(2),col143 varchar2(2),col144 varchar2(2),col145 varchar2(2),
col146 varchar2(2),col147 varchar2(2),col148 varchar2(2),col149 varchar2(2),
col150 varchar2(2),col151 varchar2(2),col152 varchar2(2),col153 varchar2(2),
col154 varchar2(2),col155 varchar2(2),col156 varchar2(2),col157 varchar2(2),
col158 varchar2(2),col159 varchar2(2),col160 varchar2(2),
col161 varchar2(2),col162 varchar2(2),col163 varchar2(2),col164 varchar2(2),
col165 varchar2(2),col166 varchar2(2),col167 varchar2(2),col168 varchar2(2),
col169 varchar2(2),col170 varchar2(2),col171 varchar2(2),col172 varchar2(2),
col173 varchar2(2),col174 varchar2(2),col175 varchar2(2),col176 varchar2(2),
col177 varchar2(2),col178 varchar2(2),col179 varchar2(2),col180 varchar2(2),
col181 varchar2(2),col182 varchar2(2),col183 varchar2(2),col184 varchar2(2),
col185 varchar2(2),col186 varchar2(2),col187 varchar2(2),
col188 varchar2(2),col189 varchar2(2),col190 varchar2(2),col191 varchar2(2),
col192 varchar2(2),col193 varchar2(2),col194 varchar2(2),col195 varchar2(2),
col196 varchar2(2),col197 varchar2(2),col198 varchar2(2),col199 varchar2(2),
col200 varchar2(2),col201 varchar2(2),col202 varchar2(2),
col203 varchar2(2),col204 varchar2(2),col205 varchar2(2),col206 varchar2(2),
col207 varchar2(2),col208 varchar2(2),col209 varchar2(2),col210 varchar2(2),
col211 varchar2(2),col212 varchar2(2),col213 varchar2(2),col214 varchar2(2),
col215 varchar2(2),col216 varchar2(2),col217 varchar2(2),col218 varchar2(2),
col219 varchar2(2),col220 varchar2(2),col221 varchar2(2),
col222 varchar2(2),col223 varchar2(2),col224 varchar2(2),col225 varchar2(2),
col226 varchar2(2),ol227 varchar2(2),col228 varchar2(2),col229 varchar2(2),
col230 varchar2(2),col231 varchar2(2),col232 varchar2(2),
col233 varchar2(2),col234 varchar2(2),col235 varchar2(2),col236 varchar2(2),
col237 varchar2(2),col238 varchar2(2),col239 varchar2(2),col240 varchar2(2),
col241 varchar2(2),col242 varchar2(2),col243 varchar2(2),col244 varchar2(2),
col245 varchar2(2),col246 varchar2(2),col247 varchar2(2),col248 varchar2(2),
col249 varchar2(2),col250 varchar2(2),col251 varchar2(2),col252 varchar2(2),
col253 varchar2(2),col254 varchar2(2),col255 varchar2(2),col256 varchar2(2),
col257 varchar2(2),col258 varchar2(2),col259 varchar2(2),col260 varchar2(2),
col261 varchar2(2),col262 varchar2(2),col263 varchar2(2),col264 varchar2(2),
col265 varchar2(2),col266 varchar2(2),col267 varchar2(2),col268 varchar2(2),
col269 varchar2(2),col270 varchar2(2),col271 varchar2(2),col272 varchar2(2),
col273 varchar2(2),col274 varchar2(2),col275 varchar2(2),col276 varchar2(2),
col277 varchar2(2),col278 varchar2(2),col279 varchar2(2),col280 varchar2(2),
col281 varchar2(2),col282 varchar2(2),col283 varchar2(2),col284 varchar2(2),
col285 varchar2(2),col286 varchar2(2),col287 varchar2(2),col288 varchar2(2),
col289 varchar2(2),col290 varchar2(2),col291 varchar2(2),col292 varchar2(2),
col293 varchar2(2),col294 varchar2(2),col295 varchar2(2),col296 varchar2(2),
col297 varchar2(2),col298 varchar2(2),col299 varchar2(2),col300 varchar2(2));

table created

SQL> insert into heyf_t1 (col1 ,col3 ,col254,col255,col256,col257,col300 ) values ('1','2','3','4','5','6','7');

1 rows inserted

SQL> commit;

commit compeleted;

SQL> select header_file,header_block from dba_segments where segment_name ='HEYF_T1'
2 ;

HEADER_FILE HEADER_BLOCK
----------- ------------
8 43

SQL> alter system dump datafile 8 block 44;

system altered.

開啟DUMP檔案來看一下：

Dump file d:oracleadminheyfudumpheyf_ora_2408.trc
Thu Jul 17 10:50:11 2008
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Instance name: heyf

Redo thread mounted by this instance: 1

Oracle process number: 16

Windows thread id: 2408, image: ORACLE.EXE

*** 2008-07-17 10:50:11.000
*** SESSION ID:(16.42) 2008-07-17 10:50:11.000
Start dump data blocks tsn: 8 file#: 8 minblk 44 maxblk 44
buffer tsn: 8 rdba: 0x0200002c (8/44)
scn: 0x0000.000ac684 seq: 0x02 flg: 0x04 tail: 0xc6840602
frmt: 0x02 chkval: 0xb713 type: 0x06=trans data
Block header dump: 0x0200002c
Object id on Block? Y
seg/obj: 0x738b csc: 0x00.ac683 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000029 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.005.00000154 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x5f9107c
===============
tsiz: 0x1f80
hsiz: 0x16
pbl: 0x05f9107c
bdba: 0x0200002c
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1e41
avsp=0x1e2b
tosp=0x1e2b
0xe:pti[0] nrow=2 offs=0　　 --nrow=2 內部連線數
0x12:pri[0] offs=0x1e74 --內部連線的兩個偏移地址
0x14:pri[1] offs=0x1e41
block_row_dump:
tab 0, row 0, @0x1e74　　　　　　　　　 --row0 第一個"行片row piece"
tl: 268 fb: --H-F--- lb: 0x0 cc: 255 --欄位數
nrid: 0x0200002c.1 --內部連線地址，在同一個塊中的1號偏移地址
col 0: [ 1] 31
col 1: *NULL*
col 2: [ 1] 32
col 3: *NULL*
col 4: *NULL*
col 5: *NULL*
col 6: *NULL*
col 7: *NULL*

... ... 省略

col 246: *NULL*
col 247: *NULL*
col 248: *NULL*
col 249: *NULL*
col 250: *NULL*
col 251: *NULL*
col 252: *NULL*
col 253: [ 1] 33
col 254: [ 1] 34
tab 0, row 1, @0x1e41 --row1 第二個"行片row piece"
tl: 51 fb: -----L-- lb: 0x0 cc: 45 --cc 欄位數
col 0: [ 1] 35
col 1: [ 1] 36
col 2: *NULL*
col 3: *NULL*
col 4: *NULL*
col 5: *NULL*
col 6: *NULL*
col 7: *NULL*
col 8: *NULL*

... ...

col 40: *NULL*
col 41: *NULL*
col 42: *NULL*
col 43: *NULL*
col 44: [ 1] 37
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 44 maxblk 44

從以上的DUMP檔案可以看到：
nrid: 0x0200002c.1 指示了塊連線的下一個地址
每一個塊片，只能最大儲存255個欄位

下面我們來再看一下塊外連線：

SQL>create table heyf_t2 (col1 char(2000),col2 char(2000),col3 char(2000),col4 char(2000),col5 char(2000));

table created

SQL> select header_file,header_block from dba_segments where segment_name ='HEYF_T2'
2 ;

HEADER_FILE HEADER_BLOCK
----------- ------------
8 35

SQL> alter system dump datafile 8 block 36;

system altered.

SQL> alter system dump datafile 8 block 37;

system altered.

SQL> alter system dump datafile 8 block 38;

system altered.

以下為DUMP結果，有幾點要特別指出：
1.在header block的下一個資料塊並沒有存放資料，資料是從第37塊開始存放的
2.資料儲存的順序是倒的：一行總共用了兩個塊，37,38塊，但38塊中存放的資料是第1個欄位
3.行連線的資訊NRID存放在第38個塊中：nrid: 0x02000025.0 檔案、塊號、偏號號

*** 2008-07-17 10:58:22.000
Start dump data blocks tsn: 8 file#: 8 minblk 36 maxblk 36
buffer tsn: 8 rdba: 0x02000024 (8/36)
scn: 0x0000.000ac8b8 seq: 0x02 flg: 0x00 tail: 0xc8b80602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x02000024
Object id on Block? Y
seg/obj: 0x738c csc: 0x00.ac8b8 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000021 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x5f91064
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x05f91064
bdba: 0x02000024
76543210
flag=--------
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
avsp=0x1f8a
tosp=0x1f8a
block_row_dump:
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 36 maxblk 36

*** 2008-07-17 10:59:23.000
Start dump data blocks tsn: 8 file#: 8 minblk 37 maxblk 37
buffer tsn: 8 rdba: 0x02000025 (8/37)
scn: 0x0000.000ac8b9 seq: 0x01 flg: 0x02 tail: 0xc8b90601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x02000025
Object id on Block? Y
seg/obj: 0x738c csc: 0x00.ac8b8 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000021 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.009.000001d0 0x00809e8d.0038.09 --U- 1 fsc 0x0000.000ac8b9
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000

data_block_dump,data header at 0x5f9107c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x05f9107c
bdba: 0x02000025
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x49
avsp=0x35
tosp=0x35
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x49
block_row_dump:
tab 0, row 0, @0x49
tl: 7991 fb: -----LP- lb: 0x1 cc: 4
col 0: [1976]
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
20
col 1: [2000]
33 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
col 2: [2000]
34 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
col 3: [2000]
35 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 37 maxblk 37

*** 2008-07-17 11:02:41.000
Start dump data blocks tsn: 8 file#: 8 minblk 38 maxblk 38
buffer tsn: 8 rdba: 0x02000026 (8/38)
scn: 0x0000.000ac8b9 seq: 0x01 flg: 0x02 tail: 0xc8b90601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x02000026
Object id on Block? Y
seg/obj: 0x738c csc: 0x00.ac8b8 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000021 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.009.000001d0 0x00809e8d.0038.0a --U- 1 fsc 0x0000.000ac8b9
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x5f91064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x05f91064
bdba: 0x02000026
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x17a3
avsp=0x178f
tosp=0x178f
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x17a3
block_row_dump:
tab 0, row 0, @0x17a3
tl: 2037 fb: --H-F--N lb: 0x1 cc: 2
nrid: 0x02000025.0 --塊外連線
col 0: [2000]
31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
.....

col 1: [24]
32 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 38 maxblk 38