Oracle SQL | Tag Archives: intra-block chaining

I’ve wrote in previous post

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.

But it’s not valid anymore 🙂 Since 12.2 Oracle optimizes updates too.
You can check it on 12.2 and previous version using example 4 from previous post:

Test 4

[sourcecode language=”sql”]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;[/sourcecode]

[collapse]

On 12.2 you will see that it creates just 5 blocks 🙂

PS. My presentation about Intra-block row chaining from RuOUG seminar: Intra-block row chaining(RuOUG)

You might be familiar with intra-block row chaining, which can occur when a table has more than 255 columns. ~~However, did you know that intra-block chaining only works with inserts, not updates?~~ (Upd: This is not valid since version 12.2).

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. A single row piece can store up to 255 columns.
2. Oracle splits fields into row pieces in reverse order.
3. Oracle doesn’t store trailing NULLs in a row (not in a row piece)
4. The next row piece can be stored in the same block only with inserts. When you run an update, oracle will place the new row piece into a different block. (not valid since 12.2)

I’ll show in examples with dumps:

Continue reading→

Tag Archives: intra-block chaining

Intra-block row chaining optimization in 12.2

row pieces, 255 columns, intra-block row chaining in details