Join Elimination und vertikale Partitionierung

Beim Nachdenken über die Möglichkeiten der Join-Elimination ist mir der Gedanke gekommen, dass man darüber eine Art vertikaler Partitionierung definieren könnte. Hier ein einfaches Beispiel dazu (in 11.1.0.7). Darin existieren drei Dimensionstabellen unterschiedlicher Granularität, die somit eigentlich sehr unterschiedliche Mengen enthalten würden. Würde man sie in ihrer natürlichen hierarchischen Ordnung miteinander verknüpfen, dann könnte eine Join Elimination nur die Parent-Tabellen in FK-Beziehungen ausschließen.

Wenn man diese Tabellen aber denormalisiert (was im DWH-Kontext ja nicht unüblich ist), indem man sie mit dem Schlüssel der Tabelle mit den meisten Sätzen erweitert (im Beispiel DIM_VARIANTEN), dann erhält man eine 1:1-Beziehung zwischen allen Dimensionen. Wenn man dann zwischen den Schlüsselspalten noch wechselseitige Foreign Key-Beziehungen definiert, kann sich der Zugriff immer auf die jeweils relevante Datengrundlage beschränken:

-- Test_Join_Elimination.sql
-- Loeschung aller zugehoerigen Objekte
alter table dim_artikel drop constraint fk_a_v;
alter table dim_artikel drop constraint fk_a_s;

alter table dim_sortiment drop constraint fk_s_v;
alter table dim_sortiment drop constraint fk_s_a;

alter table dim_variante drop constraint fk_v_s;
alter table dim_variante drop constraint fk_v_a;

drop table dim_sortiment;
drop table dim_variante;
drop table dim_artikel;

-- Anlage der Dimensionstabellen mit jeweils 100M Saetzen
create table dim_variante tablespace test_ts
as
select rownum var_id
  from dual 
connect by level <= 100000;

create table dim_artikel tablespace test_ts
as
select rownum var_id
     , substr(rownum, 1, 4) art_id
     , mod(rownum, 4) + 1 saison
  from dual 
connect by level <= 100000;

create table dim_sortiment tablespace test_ts
as
select rownum var_id
     , mod(rownum, 100) + 1 wgr_id
     , 'WGR ' || to_char(mod(rownum, 100) + 1) wgr_name   
  from dual 
connect by level <= 100000;

-- Anlage von PKs und FKs
alter table dim_variante add constraint pk_dim_variante primary key (var_id);
alter table dim_artikel add constraint pk_dim_artikel primary key (var_id);
alter table dim_sortiment add constraint pk_dim_sortiment primary key (var_id);

alter table dim_artikel add constraint fk_a_v foreign key (var_id) references dim_variante(var_id);
alter table dim_artikel add constraint fk_a_s foreign key (var_id) references dim_sortiment(var_id);

alter table dim_sortiment add constraint fk_s_v foreign key (var_id) references dim_variante(var_id);
alter table dim_sortiment add constraint fk_s_a foreign key (var_id) references dim_artikel(var_id);

alter table dim_variante add constraint fk_v_s foreign key (var_id) references dim_sortiment(var_id);
alter table dim_variante add constraint fk_v_a foreign key (var_id) references dim_artikel(var_id);

-- Statistikerhebung
exec dbms_stats.gather_table_stats(user, 'DIM_ARTIKEL')
exec dbms_stats.gather_table_stats(user, 'DIM_SORTIMENT')
exec dbms_stats.gather_table_stats(user, 'DIM_VARIANTE')

-- View-Anlage
create or replace view dim_variante_gesamt
as
select v.var_id 
     , a.art_id
     , s.wgr_id
     , s.wgr_name
     , a.saison
  from dim_variante v 
     , dim_sortiment s
     , dim_artikel a
 where v.var_id = a.var_id
   and a.var_id = s.var_id;

-- Ermittlung der Pläne
explain plan for
select distinct saison
  from dim_variante_gesamt;

select * from table(dbms_xplan.display(null, null, 'basic'));

explain plan for
select distinct wgr_name
  from dim_variante_gesamt;

select * from table(dbms_xplan.display(null, null, 'basic'));

explain plan for
select distinct var_id
  from dim_variante_gesamt;

select * from table(dbms_xplan.display(null, null, 'basic'));

explain plan for
select distinct wgr_name, saison
  from dim_variante_gesamt;
  
select * from table(dbms_xplan.display(null, null, 'basic'));

Das Script liefert folgendes Ergebnis:

PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 1748037202

------------------------------------------
| Id  | Operation          | Name        |
------------------------------------------
|   0 | SELECT STATEMENT   |             |
|   1 |  HASH UNIQUE       |             |
|   2 |   TABLE ACCESS FULL| DIM_ARTIKEL |
------------------------------------------



PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 762726623

--------------------------------------------
| Id  | Operation          | Name          |
--------------------------------------------
|   0 | SELECT STATEMENT   |               |
|   1 |  HASH UNIQUE       |               |
|   2 |   TABLE ACCESS FULL| DIM_SORTIMENT |
--------------------------------------------



PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 674027647

------------------------------------------
| Id  | Operation         | Name         |
------------------------------------------
|   0 | SELECT STATEMENT  |              |
|   1 |  TABLE ACCESS FULL| DIM_VARIANTE |
------------------------------------------



PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 1066181588

---------------------------------------------
| Id  | Operation           | Name          |
---------------------------------------------
|   0 | SELECT STATEMENT    |               |
|   1 |  HASH UNIQUE        |               |
|   2 |   HASH JOIN         |               |
|   3 |    TABLE ACCESS FULL| DIM_ARTIKEL   |
|   4 |    TABLE ACCESS FULL| DIM_SORTIMENT |
---------------------------------------------

Demnach werden tatsächlich jeweils nur die Tabellen gelesen, die die erforderlichen Daten enthalten. Auf diese Weise kann man eine große Tabelle mit mehreren relativ unabhängigen Spaltengruppen (die ihrerseits eng miteinander zusammenhängen) in mehrere Segmente aufsplitten: also in gewisser Weise vertikal partitionieren. Allerdings wird in vielen Fällen die Verwendung geeigneter Indizes wahrscheinlich die näher liegende Vorgehensweise sein. Nachdem ich dieses Beispiel gebastelt hatte, kam mir dann auch noch der Gedanke, an den üblichen Stellen nach ähnlichen Beispielen zu suchen - und wahrscheinlich hält sich die Überraschung in Grenzen, wenn ich verrate, dass der Herr Kyte etwas Passendes im Angebot hat.