Table Compression und Sortierung

Vor einigen Tagen habe ich in einer Datenbank einen Effekt beobachtet, den ich nicht erwartet hatte: ich wollte dort die Komprimierung einer zweispaltigen Tabelle dadurch verbessern, dass ich die Daten nach der Spalte mit der geringeren Anzahl distinkter Werte sortieren ließ - aber tatsächlich verschlechterte sich die Komprmierung dadurch sogar marginal. Grund genug, das Verhalten noch mal genauer anzuschauen. Bei Ordix findet man eine hübsche Darstellung des Komprimierungsverfahrens:

Die Komprimierung erfolgt auf Blockebene. Mehrfacheinträge im Datenblock werden nur einmal gespeichert. Ein Pointer im Datenteil des Blocks referenziert auf eine Symboltabelle, in der die sich wiederholenden Werte gespeichert werden. Diese Symboltabelle befindet sich in einem dafür reservierten Bereich innerhalb des Blocks. Der restliche Platz steht den eigentlichen Daten zur Verfügung. [...]

Diese Methode, die bereits bei der BulkCompression-Methode von Oracle 9i verwendet wurde, wird technisch identisch ebenfalls für die neue Methode verwendet: compress for all operations

Ich bleibe erst mal bei der einfachen BulkCompression. Hier ein Beispiel, von dem ich annahm, dass es das oben geschilderte Verhalten reproduzieren könnte (Windows 7, Oracle 11.2, 8k blocksize): Ich lege drei Tabellen an, eine sortiert nach der ersten Spalte mit 1.000.000 Ausprägungen, eine sortiert nach der zweiten Spalte mit 1.000 Ausprägungen und eine sortiert nach Zufall (in der Gestalt von DBMS_RANDOM.VALUE):

create table compress_num_ord_col1 compress
as
select level rn
     , mod(level, 1000) col1
  from dual
connect by level <= 1000000
 order by mod(level, 1000);
 
exec dbms_stats.gather_table_stats(user, 'compress_num_ord_col1')
 
create table compress_num_ord_rn compress
as
select level rn
     , mod(level, 1000) col1
  from dual
connect by level <= 1000000
 order by level;
 
exec dbms_stats.gather_table_stats(user, 'compress_num_ord_rn')

create table compress_num_ord_random compress
as
select level rn
     , mod(level, 1000) col1
  from dual
connect by level <= 1000000
 order by dbms_random.value;
 
exec dbms_stats.gather_table_stats(user, 'compress_num_ord_random')

select table_name
     , pct_free
     , blocks
    , compression
    , compress_for
  from user_tables
 where table_name like 'COMPRESS_NUM%';

TABLE_NAME                       PCT_FREE     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
COMPRESS_NUM_ORD_COL1                   0       1528 ENABLED  BASIC
COMPRESS_NUM_ORD_RANDOM                 0       1760 ENABLED  BASIC
COMPRESS_NUM_ORD_RN                     0       1763 ENABLED  BASIC

Interessant ist zunächst, dass der PCT_FREE-Wert 0 ist, was laut Doku zu den Tricks der Compression gehört: "Tables with COMPRESS or COMPRESS BASIC use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause." Noch interessanter ist, dass das Ergebnis meinen ursprünglichen Vorstellungen entspricht, aber nicht den Beobachtungen, die den Test anregten. Wahrscheinlich sollte ich den Test im fraglichen System (10.2.0.4) noch mal wiederholen.

Im Moment interessiert mich aber noch eine andere Frage: was sind "Mehrfacheinträge im Datenblock"? Handelt es sich dabei um komplette Spaltenwerte oder können auch Teilstrings durch Platzhalter ersetzt werden. Dazu folgendes (nicht eben elegantes) Beispiel: diesmal lege ich vier Tabellen an, in denen jeweils ein längerer String mit unterschiedlichen Werten konkateniert wird:

create table compress_str_1_nocomp
as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || 1000000 col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'compress_str_1_nocomp')

create table compress_str_1000000 compress
as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || (1000000 + level) col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'compress_str_1000000')

create table compress_str_1000 compress
as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || (1000000 + mod(level, 1000)) col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'compress_str_1000')

create table compress_str_100 compress
as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || (1000000 + mod(level, 100)) col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'compress_str_100')


create table compress_str_1 compress
as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || 1000000 col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'compress_str_1')

select table_name
     , num_distinct
  from user_tab_cols
 where column_name = 'COL1'
   and table_name like 'COMPRESS_STR%';
   
TABLE_NAME                     NUM_DISTINCT
------------------------------ ------------
COMPRESS_STR_1                            1
COMPRESS_STR_100                        100
COMPRESS_STR_1000                      1000
COMPRESS_STR_1000000                1000000

select table_name
     , pct_free
     , blocks
    , compression
    , compress_for
  from user_tables
 where table_name like 'COMPRESS_STR%';
 
TABLE_NAME                       PCT_FREE     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
COMPRESS_STR_1_NOCOMP                  10       5500 DISABLED
COMPRESS_STR_1000000                    0       4941 ENABLED  BASIC
COMPRESS_STR_1000                       0       4941 ENABLED  BASIC
COMPRESS_STR_100                        0       2655 ENABLED  BASIC
COMPRESS_STR_1                          0       1408 ENABLED  BASIC

Der Test ist nicht gerade präzise, aber ich interpretiere die Ergebnisse so, dass tatsächlich nur komplette Strings ersetzt werden: jeder Block enthält für COMPRESS_STR_1_NOCOMP 185 Sätze. Da für COMPRESS_STR_100 jeder hundertste Satz identisch ist, kann hier eine Komprimierung erfolgen, während die Fälle, in denen die Duplikate weiter auseinander liegen (COMPRESS_STR_1000, COMPRESS_STR_1000000) keine Komprimierung erfahren. Möglicherweise könnte ein Blockdump das Verfahren noch etwas deutlicher beleuchten - und möglicherweise probiere ich das gelegentlich noch aus.