Hatékony eljárás aktív dimenzió rekordok történetének lekérdezésére.

A minap belefutottam egy feladatba, melyben arra volt szükség, hogy egy dimenzió táblából leválogassam azon elemek történetiségét, melyeknek van éppen érvényes rekordja. Első nekifutásra az alábbi megoldással álltam elő:

create table dim_table
(
  code_1 number,
  desc_1 varchar2(200),
  start_of_validity date,
  end_of_validity date
);

select
  hist.code_1,
  hist.desc_1,
  hist.start_of_validity,
  hist.end_of_validity
from
  dim_table curr,
  dim_table hist
where curr.start_of_validity <= sysdate
  and curr.end_of_validity > sysdate
  and curr.code_1 = hist.code_1;

Mivel hatalmas tábláról volt szó és több hónapra visszamenőlegesen kellett futtatni a feldolgozást, ezért elkezdtem gondolkodni rajta, hogyan lehet lefaragni a futási időből. Az alábbi megoldással rukkoltam elő:

select
  code_1,
  desc_1,
  start_of_validity,
  end_of_validity
from (
  select
    curr.code_1,
    curr.desc_1,
    curr.start_of_validity,
    curr.end_of_validity,
    max(case
      when curr.start_of_validity <= sysdate and curr.end_of_validity > sysdate then 1
      else 0
    end) over (partition by code_1) as curr_ind
  from
    dim_table curr
)
where curr_ind = 1;

Ez utóbbi megoldás ugyanazt az eredményhalmazt adja, azonban a végrehajtás során csak egyszer kell felolvasni az alaptáblát. Az én esetemben ez közel megfelezte a végrehajtási időt.