Segment compression und pctfree

Ein kurzer Test zum Verhältnis von segment compression und PCTFREE-Einstellung von Tabellen.

Zur Erinnerung: segment compression ist das seit 9i verfügbare Feature zur Komprimierung von Tabellen, das wiederholte Einträge innerhalb eines Blocks durch Platzhalter ersetzt. Es funktioniert nur für direct path inserts, direct path SQL*Loader- und CTAS-Operationen. Nicht zu verwechseln ist es mir der (kostenpflichtigen) advanced compression in Version 11, die alle DML-Operationen unterstützt.

Über PCTFREE steuert man den initialen Füllgrad einer Tabelle, gibt also an, wie viel Platz zunächst frei bleiben soll, damit folgende UPDATEs nicht zu Row-Migration führen.

Mein Test prüft, wie sich die Größe einer Tabelle abhängig vom Einsatz der beiden Optionen verändert:

-- Anlage einer Testtabelle mit drei Spalten,
-- von denen col2 immer gleich gefüllt ist
-- und gut komprimierbar sein sollte
create table test
as
select rownum rn
     , mod(rownum , 10) col1
     , lpad('*', 50, '*') col2
  from dual
connect by level < 100000

Über CTAS erstelle ich nun mehrere Kopien dieser Tabelle mit unterschiedlichen PCTFREE- und compress-Einstellungen:

create table test_pctfree0_nocompress pctfree 0
as
select *
  from test;

create table test_pctfree0_compress pctfree 0 compress
as
select *
  from test;

create table test_pctfree50_nocompress pctfree 50
as
select *
  from test;

create table test_pctfree50_compress pctfree 50 compress
as
select *
  from test;

-- Erzeugung von Statistiken für alle Tabellen
exec dbms_stats.gather_table_stats(user, 'TEST_PCTFREE0_COMPRESS') 
...

In USER_TABLES finden sich nun folgende Block-Angaben:

TABLE_NAME                       PCT_FREE     BLOCKS
------------------------------ ---------- ----------
TEST                                   10        451  -- Standardwert: PCTFREE 10
TEST_PCTFREE0_NOCOMPRESS                0        406  -- 451 minus 10%
TEST_PCTFREE50_NOCOMPRESS              50        811  -- 406 * 2
TEST_PCTFREE0_COMPRESS                  0         82  -- ca. 20% von 406
TEST_PCTFREE50_COMPRESS                50        158  -- ca. 20% von 811

Die Zahlen entsprechen recht exakt den Erwartungen: die Kompression reduziert die Netto-Datenmenge (im gegebenen Fall auf ca. 20%) und PCTFREE sorgt dann dafür, dass jeder Block den vorgesehenen Erweiterungsspielraum erhält. Die beiden Features sind also unabhängig voneinander.