You may know about Intra-block row chaining, that may occur when the number of columns in a table is more than 255 columns.
But do you know that intra-block chaining works with inserts only? not updates!
Documentation says:
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.
A bit more details:
1. One row piece can store up to 255 columns
2. Oracle splits fields by row pieces in reverse order
3. Oracle doesn’t store trailing null fields in a row (not in row piece)
4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block. (not valid since 12.2)
I’ll show in examples with dumps:
Example 1:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_300) values(2)
3. dump data blocks
test code
drop table test purge;
set serverout on;
alter session set tracefile_identifier='test1';
declare
c varchar2(32000);
v varchar2(32000);
cols varchar2(32000):='c_1 number(1,0)';
vals varchar2(32000):='null';
ndf int;
nbl int;
begin
for i in 2..355 loop
cols:=cols||',c_'||i||' number(1,0)';
vals:=vals||','||case
when i = 300 then '2'
else 'null'
end;
end loop;
c:='create table test('||cols||')';
v:='insert into test values('||vals||')';
dbms_output.put_line(c);
dbms_output.put_line(v);
execute immediate (c);
execute immediate (v);
for r in (select 'alter system dump datafile '||file#||' block '||block# cmd
from (
select distinct file#,block#
from v$bh
where class#=1
and objd in (select o.data_object_id from user_objects o where object_name='TEST')
order by 1,2
)
)
loop
execute immediate r.cmd;
end loop;
end;
/
disc;
[collapse]
bdba: 0x018019f3
block_row_dump:
bdba: 0x018019f4
block_row_dump:
bdba: 0x018019f5
block_row_dump:
bdba: 0x018019f6
block_row_dump:
bdba: 0x018019f7
block_row_dump:
tl: 260 fb: -----L-- lb: 0x1 cc: 255
col 0: *NULL*
col 1: *NULL*
col 2: *NULL*
col 3: *NULL*
...
col 252: *NULL*
col 253: *NULL*
col 254: [ 2] c1 03
tl: 54 fb: --H-F--- lb: 0x1 cc: 45
col 0: *NULL*
col 1: *NULL*
col 2: *NULL*
col 3: *NULL*
...
col 42: *NULL*
col 43: *NULL*
col 44: *NULL*
The resulted dump file shows us:
1. Both row pieces are in the same block 0x018019f4
2. They contain only first 300 columns, (trailing 55 columns are NULLs)
3. First row piece contains columns c_46 – c_300,
4. Second row piece contains columns c_1 – c_45 (they all are NULLs)
Example 2.
But let’s test an update with the same table:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_1) values(null)
3. update test set c_300=2
4. dump data blocks
Test code 2
drop table test purge;
set serverout on;
alter session set tracefile_identifier='test2';
declare
c varchar2(32000);
cols varchar2(32000):='c_1 number(1,0)';
begin
for i in 2..355 loop
cols:=cols||',c_'||i||' number(1,0)';
end loop;
c:='create table test('||cols||')';
execute immediate (c);
execute immediate ('insert into test(c_1) values(null)');
execute immediate 'update test set c_300=3';
commit;
for r in (select 'alter system dump datafile '||file#||' block '||block# cmd
from (
select distinct file#,block#
from v$bh
where class#=1
and objd in (select o.data_object_id from user_objects o where object_name='TEST')
order by 1,2
)
)
loop
execute immediate r.cmd;
end loop;
end;
/
disc;
[collapse]
bdba: 0x018019f3
block_row_dump:
tl: 260 fb: -----L-- lb: 0x1 cc: 255
col 0: *NULL*
col 1: *NULL*
col 2: *NULL*
...
col 251: *NULL*
col 252: *NULL*
col 253: *NULL*
col 254: [ 2] c1 04
bdba: 0x018019f4
block_row_dump:
bdba: 0x018019f5
block_row_dump:
bdba: 0x018019f6
block_row_dump:
bdba: 0x018019f7
block_row_dump:
tl: 54 fb: --H-F--- lb: 0x1 cc: 45
col 0: *NULL*
col 1: *NULL*
col 2: *NULL*
...
col 42: *NULL*
col 43: *NULL*
col 44: *NULL*
As you can see, there is no intra-block chaining – second row piece was created in another block.
Example 3.
Now I want to show how oracle splits already chained rows:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_1) values(1)
3. update test set c_300=2
4. update test set c_301=3
5. update test set c_302=4
6. dump data blocks
Test code 3
drop table test purge;
set serverout on
alter session set tracefile_identifier='test3';
declare
cols varchar2(32000):='c_1 number(1,0)';
procedure print_and_exec(c varchar2) as
begin
dbms_output.put_line(c);
execute immediate c;
end;
begin
for i in 2..355 loop
cols:=cols||',c_'||i||' number(1,0)';
end loop;
print_and_exec ('create table test('||cols||')');
print_and_exec ('insert into test(c_1) values(1)');
print_and_exec ('update test set c_300=2');
print_and_exec ('update test set c_301=3');
print_and_exec ('update test set c_302=4');
commit;
execute immediate 'alter system flush buffer_cache';
execute immediate 'select count(*) from test';
for r in (select 'alter system dump datafile '||file#||' block '||block# cmd
from (
select distinct file#,block#
from v$bh
where class#=1
and objd in (select o.data_object_id from user_objects o where object_name='TEST')
order by 1,2
)
)
loop
execute immediate r.cmd;
end loop;
end;
/
disc;
[collapse]
bdba: 0x018019f3
block_row_dump:
tl: 10 fb: -------- lb: 0x1 cc: 1
col 0: *NULL*
bdba: 0x018019f4
block_row_dump:
tl: 264 fb: -----L-- lb: 0x1 cc: 255
col 0: *NULL*
col 1: *NULL*
...
col 249: *NULL*
col 250: *NULL*
col 251: *NULL*
col 252: [ 2] c1 03
col 253: [ 2] c1 04
col 254: [ 2] c1 05
bdba: 0x018019f5
block_row_dump:
bdba: 0x018019f6
block_row_dump:
tl: 10 fb: -------- lb: 0x1 cc: 1
col 0: *NULL*
bdba: 0x018019f7
block_row_dump:
tl: 56 fb: --H-F--- lb: 0x1 cc: 45
col 0: [ 2] c1 02
col 1: *NULL*
col 2: *NULL*
col 3: *NULL*
...
col 42: *NULL*
col 43: *NULL*
col 44: *NULL*
This dump shows us 4 row pieces: First row piece contains 255 columns, second – 45, and 2 row pieces – just by one row.
So we can analyze it step-by-step:
2. insert into test(c_1) values(1)
After insert we have just one row piece with 1 field.
3. update test set c_300=2
After this update, we have 2 row pieces:
1) c_1-c_45
2) c_46-c_300
4. update test set c_301=3
This update split row piece c_46-c_300 into 2 row pieces:
1) c_46
2) c_47-c_301
So we have 3 row pieces now: c_1-c_45, c_46, c_47-c_301
5. update test set c_302=4
This update split row piece c_47-c_301 into 2 row pieces:
1) c_47
2) c_48-c_302
And we’ve got 4 row pieces: c_1-c_45, c_46, c_47, c_48-c_302
You can try Example 4 and see how many blocks you can get, and all of them (except last one) will have only 1 column each:
Test code 4
drop table test purge;
set serverout on
alter session set tracefile_identifier='test4';
declare
cols varchar2(32000):='c_1 char(3)';
procedure print_and_exec(c varchar2) as
begin
dbms_output.put_line(c);
execute immediate c;
end;
begin
for i in 2..355 loop
cols:=cols||',c_'||i||' char(3)';
end loop;
print_and_exec ('create table test('||cols||')');
print_and_exec ('insert into test(c_1) values(null)');
commit;
for i in 256..355 loop
execute immediate 'update test set c_'||i||'='||i;
end loop;
commit;
execute immediate 'alter system flush buffer_cache';
for r in (select 'alter system dump datafile '||file#||' block '||block# cmd
from (
select distinct file#,block#
from v$bh
where class#=1
and objd in (select o.data_object_id from user_objects o where object_name='TEST')
order by 1,2
)
)
loop
execute immediate r.cmd;
end loop;
end;
/
disc;
[collapse]
grep
bdba: 0x01801281 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801282 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801283 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801284 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801285 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801286 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801287 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801288 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801289 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128a block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128b block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128c block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128d block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128e block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128f block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801291 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801292 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801293 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801294 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801295 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801296 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801297 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801298 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801299 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129a block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129b block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129c block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129d block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129e block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129f block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a1 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a2 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a3 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a4 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a5 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a6 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a7 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a8 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a9 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012aa block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ab block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ac block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ad block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ae block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012af block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b1 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b2 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b3 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b4 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b5 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b6 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b7 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b8 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b9 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ba block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012bb block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012bc block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012bd block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012be block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012bf block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c1 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c2 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c3 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c4 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c5 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c6 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c7 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c8 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c9 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ca block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012cb block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012cc block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012cd block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ce block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012cf block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d1 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d2 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d3 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d4 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d5 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d6 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d7 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d8 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d9 block_row_dump: bdba: 0x018012da block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012db block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012dc block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012dd block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012de block_row_dump: tl: 558 fb: -----L-- lb: 0x1 cc: 255 col 0: *NULL* col 1: *NULL* col 2: *NULL* ... col 152: *NULL* col 153: *NULL* col 154: *NULL* col 155: [ 3] 32 35 36 col 156: [ 3] 32 35 37 col 157: [ 3] 32 35 38 col 158: [ 3] 32 35 39 ... col 251: [ 3] 33 35 32 col 252: [ 3] 33 35 33 col 253: [ 3] 33 35 34 col 254: [ 3] 33 35 35 bdba: 0x018012df block_row_dump: bdba: 0x018019f3 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019f4 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019f5 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019f6 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019f7 block_row_dump: tl: 10 fb: --H-F--- lb: 0x2 cc: 1 col 0: *NULL* bdba: 0x018019f8 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019f9 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019fa block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019fb block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019fc block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019fd block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019fe block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019ff block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL*
[collapse]