Oracle SQL

    Tag Archives: row chaining

    Intra-block row chaining optimization in 12.2

    Posted on June 17, 2017 by Sayan Malakshinov Posted in 12c, oracle, trace, undocumented 3 Comments

    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

    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]

    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)

    intra-block chaining row chaining

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

    Posted on February 12, 2017 by Sayan Malakshinov Posted in oracle, trace, undocumented 10 Comments

    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:
    Continue reading→

    intra-block chaining row chaining

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    Russia / Moscow / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 4 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Top-N again: fetch first N rows only vs rownum
    • Docker with Oracle database: install patches automatically
    • Top N biggest tables (with lobs, indexes and nested table)
    • “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Email Subscription

    Recent Comments

    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”
    • Adaptive serial direct path read decision ignores object statistics since 12.1 - SSWUG.ORG on Adaptive serial direct path read decision ignores object statistics since 12.1
    • Oracle issues after upgrade to 12.2 - SSWUG.ORG on Oracle issues after upgrade to 12.2
    • Ampersand instead of colon for bind variables - SSWUG.ORG on Ampersand instead of colon for bind variables
    • Евгений Бабин on Oracle issues after upgrade to 12.2
    • Oracle SQL | How even empty trigger increases redo generation on Triggers and Redo: changes on 12.2

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries RSS
    • Comments RSS
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL