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:
[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]
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)