Table Compression und Wiederholungen

Es ist schon eine Weile her, dass ich intensiver über die technischen Details von table compression nachgedacht habe, so dass die folgenden Tests und Überlegungen wahrscheinlich manches wiederholen, was ich schon früher aufgeschrieben habe, aber das nehme ich billigend in Kauf. Ausgangspunkt meiner Überlegungen ist diesmal die Frage, welche Wirkung die Wiederholung der gleichen Werte in mehreren Spalten eines Datensatzes auf die Komprimierung hat. Dazu noch mal eine kurze Beschreibung der Grundlagen des Verfahrens: Oracle legt im komprimierten Block eine Symboltabelle für wiederholte Einträge an, deren Angaben in den einzelnen rows referenziert werden. Demnach ist zu erwarten, dass sich eine breite Struktur mit mehreren Spalten und identischen Attributwerten (mindestens) genauso gut komprimieren lässt wie eine schmale Struktur mit einer einzigen Spalte mit entsprechenden Wiederholungen. Dazu ein Test (11.1.0.7, db_block_size: 8K, ASSM), bei dem die Testtabellen zunächst ohne compression angelegt werden:

-- Anlage einer ersten Testtabelle
-- mit 1M rows und einer Spalte (Breite: 50 Byte)
-- Die Spalte wird mit 50 Sternen (*) gefüllt
drop table test_col1_mpr;
create table test_col1_mpr
as
select lpad('*', 50, '*') pad1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'TEST_COL1_MPR')

-- Anlage einer ersten Testtabelle
-- mit 100K rows und 10 Spalten (Breite: jeweils 50 Byte; insgesamt also 500 Bytes)
-- Jede Spalte wird mit 50 Sternen (*) gefüllt
drop table test_col10_mpr;
create table test_col10_mpr
as
select lpad('*', 50, '*') pad1
     , lpad('*', 50, '*') pad2
     , lpad('*', 50, '*') pad3
     , lpad('*', 50, '*') pad4
     , lpad('*', 50, '*') pad5
     , lpad('*', 50, '*') pad6
     , lpad('*', 50, '*') pad7
     , lpad('*', 50, '*') pad8
     , lpad('*', 50, '*') pad9
     , lpad('*', 50, '*') pad10
  from dual
connect by level <= 100000;

exec dbms_stats.gather_table_stats(user, 'TEST_COL10_MPR')

Dazu ein paar Statistikwerte:

select table_name
     , pct_free
     , num_rows
     , blocks
     , avg_row_len
  from user_tables
 where table_name like 'TEST_COL%';

TABLE_NAME                       PCT_FREE   NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- ---------- -----------
TEST_COL10_MPR                         10     100000       7265         510
TEST_COL1_MPR                          10    1000000       7884          51

Dabei entspricht die AVG_ROW_LEN den Erwartungen, aber die Differenz bei den BLOCKS bedarf einer Erklärung:

with
basedata as (
select blockid
     , count(*) row_count
  from (select dbms_rowid.rowid_block_number(rowid) blockid
          from TEST_COL1_MPR)
 group by blockid
)
select row_count
     , count(*) cnt
  from basedata
 group by row_count
 order by row_count;

ROW_COUNT        CNT
--------- ----------
      121          1
      129       7751

with
basedata as (
select blockid
     , count(*) row_count
  from (select dbms_rowid.rowid_block_number(rowid) blockid
          from TEST_COL10_MPR)
 group by blockid
)
select row_count
     , count(*) cnt
  from basedata
 group by row_count
 order by row_count

ROW_COUNT        CNT
--------- ----------
       12          1
       14       7142

(Die Abweichung zwischen der über dbms_stats ermittelten BLOCKS-Angabe und der über dbms_rowid bestimmten Block-Anzahl schiebe ich dabei ohne weitere Prüfung auf dbms_stats)

Demnach packt Oracle also in der Regel 129 rows mit einer Breite von 51 Byte in einen Block der Tabelle TEST_COL1_MPR (129 * 51 = 6579), und 14 rows mit einer Breite von 510 Byte in die Blocks von TEST_COL10_MPR ( 14 * 510 = 7140). Angesichts des (default) PCT_FREE-Werts von 10, leuchtet mir die Füllung von TEST_COL10_MPR, aber TEST_COL1_MPR scheint deutlich weniger Sätze in jeden Block unterzubringen als möglich wäre - eine Beobachtung, die ich hier auch schon mal erwähnt und auf den Speicherplatzbedarf des row directories geschoben hatte. Bei Jonathan Lewis gibt's noch ein paar präzisere Erläuterungen zu diesem Aspekt. Demnach benötigt ein Satz insgesamt 5 Byte im row directory:

• size of each pointer in the row directory: 2 bytes
• minimum size of the “row”: 2 bytes (“flag” byte + “lock” byte)
• one extra byte for the column count – which will be zero for a completely null row

Wenn man diesen Wert in die Berechnung einbaut, nähern sich die Ergebnisse für die Testtabellen deutlich aneinender an:

• 129 * (51 + 5) = 7224
• 14 * (510 + 5) = 7210

Um die Untersuchung etwas stärker abzurunden, könnte man jetzt noch einen Test mit PCT_FREE = 0 und ohne ASSM durchführen, aber dazu fehlt mir gerade der Ehrgeiz - grundsätzlich kommt man jedenfalls recht genau auf die 8K, die im Block verfügbar sind (zu berücksichtigen wäre auch noch der Block-Header mit 57 Bytes). Stattdessen zurück zum Thema: wie gut lassen sich die beiden Test-Tabellen mit ihren überaus einheitlichen (und langweiligen) Inhalten komprimieren:

-- compression aktivieren
alter table TEST_COL1_MPR compress;
alter table TEST_COL10_MPR compress;

-- Tabellen reorganisieren (erst dann wird die compression wirksam)
alter table TEST_COL1_MPR move;
alter table TEST_COL10_MPR move;

-- Statistikerfassung
exec dbms_stats.gather_table_stats(user, 'TEST_COL1_MPR')
exec dbms_stats.gather_table_stats(user, 'TEST_COL10_MPR')

select table_name
     , num_rows
     , blocks
     , avg_row_len
  from user_tables
 where table_name like 'TEST_COL%';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
TEST_COL10_MPR                     100000        151         510
TEST_COL1_MPR                     1000000       1414          51

Nach den bisherigen Beobachtungen ist das jetzt ein einleuchtendes Ergebnis: die breitere Tabelle lässt sich deutlich stärker komprimieren, da sie die geringere Satzanzahl enthält und daher ein geringer Overhead für das (nicht komprimierbare) row directory entsteht. Pro Block lassen sich jetzt für beide Tabellen nahezu gleich viele komprimierte Sätze unterbringen:

-- TEST_COL1_MPR 
ROW_COUNT        CNT
--------- ----------
      171          1
      709          1
      724       1380

-- TEST_COL10_MPR
ROW_COUNT        CNT
--------- ----------
      503          1
      720          1
      721        137

Als Ergebnis halte ich folgendes fest:

• wiederholte Werte lassen sich Attribut-übergreifend komprimieren (wie nicht anders zu erwarten war)
• breite Tabellen mit vielen Attributen und einer geringeren Satzanzahl lassen sich besser komprimieren als schmale Tabellen mit einer höheren Satzanzahl und wenigen Attributen, sofern es viele wiederholte Werte gibt. Verantwortlich dafür ist das row directory, dessen Platzbedarf von der Komprimierung nicht beeinflusst wird.