行的存储（块内连接与块外连接）

最新推荐文章于 2021-06-21 22:25:23 发布

cndsws0715

最新推荐文章于 2021-06-21 22:25:23 发布

阅读量220

点赞数

文章标签：数据库

都说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