ValidTo anti-pattern, Oracle9

A sztori, probléma a következő:
Meg kell oldani, hogy az adatbázisban a rekordoknak legyen érvényességi ideje, és lehessen jelölni, hogy egy rekord törölt vagy nem. A törlésre is vannak patternek, töröljünk-e fizikailag, vagy ne, jelöljük csak töröltnek a rekordot, stb. Ez egy másik sztori, sok kérdés felmerül minden megoldással kapcsolatban.
Elkövettük azt a hibát, hogy rámentünk a szépségre, és a kettő problémát összemostuk. Egy mezővel jelöljük, hogy a rekord érvényes-e, ez a mező a ValidTo. Nem egy bonyolult matek, ha null akkor érvényes, vagy ha a ValidTo dátum kisebb mint az aktuális, akkor is érvényes. Ha a ValidTo kisebb mint az aktuális, akkor vagy törölt, vagy lejárt (mint pl egy beállítás).

Tök jónak tűnik, és igazából a szűrésekben nem ad hozzá extra bonyolultságot, csak ennyit kell tenni a where feltételekbe:

(t.ValidTo is NULL or t.ValidTo > now)

Mi ezzel a baj?
A baj akkor kezdődik, amikor sok nagy táblát kapcsolunk össze (4-nél többet), akkor a performanciának odavág rendesen. A ValidTo mező ugyanis egy ősosztályban van, és sajnos joined-subclass tábla hierarchia van alatta, vagyis van 1 darab ős tábla, ami kacifántos join-okkal kapcsolódik a kívánt entitás táblákhoz.

Hogy miért lassú, egyelőre az Oracle9i-n sikerült kinyomoznom. Hiába van index ezen a bizonyos ValidTo mezőn, ha arra szűrünk hogy is null vagy is not null, akkor az oracle semmi pénzért nem hajlandó az indexet használni, még hintelve sem. Ehelyett FULL TABLE ACCESS van, ami 10millió sor felett finoman megfogalmazva "nem kapkodós". Refaktoráltam a kódot, hogy ez a mező ne legyen nullozható, és ha nincs kitöltve, akkor defaultból legyen now+200 év (akkor már ez a rendszer nem fog működni, ha meg igen, akkor majd refaktorolunk :) )
Ezzel együtt a lekérdezésekből kinyírtam az IS NULL vizsgálatot és az OR-t, és voállá.
Nem valami szép, hogy kitöltjük úgymond szeméttel, de így használja a megfelelő indexe(ke)t, és join-onként egy index seek-el kevesebb az execution plan szerint is (vagy oracle-ben ahogy hívják, explain plan).
MSSQL-ben is kipróbáltam, ott ennyire nem volt látványos a gyorsulás mint az oracle 9-ben, de azért ott is tetemes. Összességében tehát ha nem a szépségre megyünk hanem a performanciára, akkor sokat számít (főleg egy amúgyis "kicsit" félredesignolt adatbázis struktúrán). Ha ez nem lesz elég, akkor valószínűleg szétválasztom a fent említett problémát, és bevezetek egy IsDeleted mezőt, mert van ahol csak azt kell vizsgálni.

UI:
Hasonló élmény volt, amikor egy csomagszállítós rendszeren dolgoztunk. Először az "X" táblában a csomag aktuális és következő helyét tároltuk, abból is ki lehetett keresni hogy milyen útvonalon ment. Nagyon sok helyen kellett self join, hogy az előző helyet (csomópontot a gráfban) megtudjuk, és ott egy olyan redesign-t alkalmaztunk, amivel bekerült a csomag előző helye is a táblába. 3 mező volt, valójában redundánsan tároltunk adatot, de nem csak a performancián segített, hanem a lekérdezések írásában és a hibakeresésben is (mármint az hogy volt previous, actual, next).