MINIMIZE RECORDS_PER_BLOCK

Noch ein paar Ergänzungen zur "MINIMIZE RECORDS_PER_BLOCK" Option, über die ich hier schon geschrieben hatte. Durch Randolf Geists Kommentar und die Erläuterungen in Julian Dykes Bitmap Index Internals Präsentation wurde deutlich, dass der Hakan Faktor einer Tabelle - der die Anzahl möglicher Sätze pro Block angibt - in der SPARE1 Spalte von SYS.TAB$ gespeichert wird, und dass die "MINIMIZE RECORDS_PER_BLOCK" Option dafür sorgt, dass das 0x8000 Bit in dieser Spalte gesetzt wird, so dass sich der dezimale Wert um 32768 erhöht. Das folgende Script prüft, wie sich der Hakan Faktor abhängig von der initialen Füllung einer Testtabelle verändert:

-- test_minimize_records_per_block.sql
undefine rowcount

set verify off
set timin off
set feedback off

-- creation of a table with  rows in a mssm tablespace
drop table t&&rowcount;

create table t&rowcount 
tablespace test_ts
as
select rownum id
  from dual
connect by level <= &rowcount;

-- gather statistics
exec dbms_stats.gather_table_stats(user, 'T&rowcount', estimate_percent=>100)

-- without minimize records_per_block
insert into T&rowcount
select rownum id
  from dual
connect by level < 10000;

exec dbms_stats.gather_table_stats(user, 'T&rowcount', estimate_percent=>100)

prompt
prompt without minimize records_per_block

column object_id new_value obj_id 
select object_id 
  from user_objects 
 where object_name = 'T&rowcount';
 
select &rowcount initial_rows
     , rowcnt
     , blkcnt
     , spare1
  from sys.tab$
 where obj# = &obj_id; 

-- with minimize records_per_block

drop table t&rowcount;

create table t&rowcount 
tablespace test_ts
as
select rownum id
  from dual
connect by level <= &rowcount;

alter table T&rowcount minimize records_per_block;

insert into T&rowcount
select rownum id
  from dual
connect by level < 10000;

exec dbms_stats.gather_table_stats(user, 'T&rowcount', estimate_percent=>100)

prompt
prompt with minimize records_per_block

column object_id new_value obj_id 
select object_id 
  from user_objects 
 where object_name = 'T&rowcount';
 
select &rowcount initial_rows
     , rowcnt
     , blkcnt
     , spare1
  from sys.tab$
 where obj# = &obj_id; 
 
set verify on
set timin on
set feedback on

Es folgen die (zusammengefassten) Ergebnisse für unterschiedliche rowcount-Angaben bei MINIMIZE RECORDS_PER_BLOCK:

INITIAL_ROWS     ROWCNT     BLKCNT     SPARE1
------------ ---------- ---------- ----------
           1      10000       5001      32769
           2      10000       5001      32769
           3      10000       3337      32770
           4      10000       2500      32771
           5      10000       2003      32772
          10      10000       1004      32777
         100      10000        103      32867

Zum Vergleich die Angaben für den Fall ohne MINIMIZE RECORDS_PER_BLOCK

    ROWCNT     BLKCNT     SPARE1
---------- ---------- ----------
     10000         10       1481

SPARE1 wird also offenbar tatsächlich um 32768 erhöht, wenn das MINIMIZE RECORDS_PER_BLOCK Kommando ausgeführt wird. Die Anzahl der Sätze pro Block ist dann offenbar SPARE1 - 32768 + 1. Interessant ist, dass auch für den Fall mit INITIAL_ROWS = 1 ein SPARE1-Wert von 32769 und 2 Rows pro Block untergebracht werden. Anscheinend ist es nicht möglich über MINIMIZE RECORDS_PER_BLOCK auf einen Satz pro Block zu kommen - sofern mehr als ein Satz in den Block hinein passt.