Big Table Joins im SQL Server

Thomas Kejser erläutert in seinem Blog die verschiedenen Möglichkeiten, große Tabellen zu joinen - wobei seine Ausführungen zu einem großen Teil auch für Oracle-Datenbanken (und andere RDBMS) zutreffen.

Der gesamte Artikel ist sehr interessant, aber besonders wichtig scheint mir der Hinweis auf die Abhängigkeit der HASH JOIN Performance vom verfügbaren Arbeitsspeicher zu sein. Dazu ein kleines Oracle-Beispiel (11.1.0.7):

-- Anlage zweier (relativ) großer Tabellen
-- mit jeweils 128 MB 
create table test_big1
as
select rownum id
     , mod(rownum, 5) col1
     , lpad('*', 100, '*') padding
  from dual
connect by level <= 1000000;

create table test_big2
as
select rownum id
     , mod(rownum, 5) col1
     , lpad('*', 100, '*') padding
  from dual
connect by level <= 1000000;

-- Statistikerfassung
exec dbms_stats.gather_table_stats(user, 'test_big1')
exec dbms_stats.gather_table_stats(user, 'test_big2')

Auf diese Tabellen greift eine recht harmlose aggrregierende Query zu:

select /*+ use_hash(t1 t2) */
       t1.col1, count(*)
  from test_big1 t1
     , test_big2 t2
 where t1.id = t2.id
 group by t1.col1;

Bei manuellem Speichermanagement und sukzessiver Verkleinerung der HASH_AREA_SIZE ergeben sich in V$SQL_WORKAREA folgende Angaben:

SQL_ID        LAST_MEMORY_USED LAST_EXECUTION   ACTIVE_TIME LAST_TEMPSEG_SIZE
------------- ---------------- ---------------- ----------- -----------------
c4mkumm56b2xs         31068160 OPTIMAL              2394526                    <-- H_A_S: 300 MB 
6nfcggd6pzpjq         11768832 1 PASS               3961418          22020096  <-- H_A_S: 100 MB 
a2ndxvbhd3fzn          1498112 1 PASS              14735056          27262976  <-- H_A_S: 10 MB 
f10jfqu1tu0bw           176128 123 PASSES         142458630          28311552  <-- H_A_S: 1 MB

Auffällig ist dabei:

• die massive Laufzeitverlängerung von 2,3 sec. für die optimale Verarbeitung (ohne Auslagerung von Zwischenergebnissen in den temporary tablespace) auf 142,4 sec. für die multi pass Operation
• der deutliche Unterschied der Laufzeiten der beiden 1 pass Ausführungen (3,9 sec. zu 14,7 sec.)

Das Ergebnis ist in keiner Weise überraschend: ausreichender Arbeitsspeicher ist für die Performance von HASH JOIN Operationen ganz entscheidend.