Materialized view support in different DBMS

Some notes regarding materialized view support in different database management systems.

Materialized view is a database object that stores a snapshot of database query.

If a regular database view could be seen as a “window” – one looks through this window and sees actual situation of the data, then “materialized view ” would be something like a “photo image” i.e., you take an image and can look at it even without going near window, but you only see situation that was on that particular moment picture was taken and if you want to update – you have to take another picture etc.

Different DBMS have different materialized view implementation, lets go thorough most popular ones and look how they are supported.

Oracle

Wikipedia states that oracle was first to implement materialized view in version 8i.

CREATE MATERIALIZED VIEW MV_MY_VIEW
REFRESH FAST START WITH SYSDATE
   NEXT SYSDATE + 1
     AS SELECT * FROM <table_name>;

PostgreSQL

PostgreSQL supports materialized view from version 9.3

CREATE MATERIALIZED VIEW MV_MY_VIEW
 [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
     AS SELECT * FROM <table_name>;

SQL Server

SQL server has different concept and it is called Indexed View. The main difference is that such views do not require a refresh because they are in fact always synchronized to the original data of the tables that compound the view. To achieve this, it is necessary that the lines of origin and destination are “deterministic” in their mapping which limits the types of possible queries to do this.

CREATE VIEW MV_MY_VIEW
WITH SCHEMABINDING
AS 
SELECT COL1, SUM(COL2) AS TOTAL
FROM <table_name>
GROUP BY COL1;
GO
CREATE UNIQUE CLUSTERED INDEX XV 
   ON MV_MY_VIEW (COL1);

MariaDB or MySQL

No native materialized view support in MariaDB or MySQL. Can be implemented using FlexiViews.

 

Reliacinė algebra ir norminės formos

Kai kurie žymėjimai ir reliacinė algebra
R={A1,A2,…,An} t.y, R yra lentelės L atributų aibė. Ai kur i=1,…,n yra lentelės atributas. Tada L(R) yra lentelės reliacinė schema.

Lentelės eilučių aibę žymima l={e1,e2,…,em} kur n – lentelės stulpelių skaičius, m – lentelės eilučių skaičius.

Kiekviena eilutė yra sutvarkytas reikšmių rinkinys ej = <a1,a2,…,an> ∈ l;

ai ∈ dom(Ai), kur dom(Ai) yra atributo Ai domenas t.y, galimų reikšmių aibė. i=1,…,n j=1,…,m

Atributų rinkinys apibūdinantis bet kurią lentelės eilutę yra vadinamas viršrakčiu. Viršraktis yra raktas jei pašalinus bent vieną atributą jis praranda universalumą. Taigi raktas yra minimali atributų aibė, vienareikšmiškai apibrėžianti visų atributų reikšmes kiekvienoje eilutėje. Jei raktą sudaro keli atributai, jis vadinamas sudėtiniu raktu.

Lentelėje gali būti keli atributų rinkiniai turintys rakto savybę, visi jei vadinami galimais raktais arba tiesiog raktais. Taigi lentelėje gali būti keli raktai. Vienas iš raktų paskelbiamas pirminiu raktu, dažniausiai trumpiausias.

Lentelės l(R)={e1,e2,…,em} atributų aibės R poaibis V⊆R yra viršraktis, jei  ∀i,j=1,…,m ir i≠j yra teisinga nelygybė ei(V)≠ej(V)

Funkcinė priklausomybė.
Kai atributų aibė A eilutėje vienareikšmiškai apibrėžia atributų aibę B, tai priklausomybė tarp A ir B atributų vadinama funkcinė ir žymima A→B.

A→B reikšia, jog A funkciškai apibrėžia B arba B funkciškai priklauso nuo A. Kairė žymėjimo pusė vadinama Determinantu.

Funkcinė priklausomybė gali būti:

  • trivialioji, kai {B1,B2,…,Bm} yra aibės {A1,A2,…,An} poaibis;
  • netrivialioji, jei egzistuoja bent vienas Bi, Bi∈{B1,…,Bm} ∧ Bi∉{A1,…,An};
  • visiškai netrivialioji, jei ∀i:1,…,m : Bi∉{A1,…,An}.

Pirmoji norminė forma

Lentelė atitinka pirmą norminę formą kai visi lentelės atributai yra nedalomi, t.y., atributuose nesaugomi sąrašai ar kitokios sudėtinės reikšmės.

Pvz., lentelė Dėstytojai={Id, Vardas,Pavardė,Mokymo_įstaigos}, kurios eilutėje saugomos tokios reikšmės {“1”, “Vardis”,”Pavardis”,”VU,KTU,VPU”} netenkina pirmos norminės formos reikalavimo nes lauke Mokymo_įstaigos saugomas reikšmių aibė. Tokią lentelę galima būtų normalizuoti į sekančia tris lenteles:

Dėstytojai={Id,Vardas,Pavardė}

Įstaigos={Id,Pavadinimas}

Dėstytojų_įstaigos={Dėstytojo_Id, Įstaigos_Id}

Visos trys naujos lentelės atitiktų 1NF.

Antroji norminė forma

Lentelė atitinka antrą norminę formą kai ji yra 1NF ir neturi tokių atributų kurie funkciškai priklausytų tik nuo rakto dalies (kai raktas yra sudėtinis).

Pavyzdžiui, turime lentelę Dėstytojų_įstaigos={Dėstytojo_Id, Įstaigos_Id,Įdarbinimo_data,Įstaigos_adresas}

Ši lentelė nėra antros norminės formos nes turime sudėtinį raktą kurį sudaro atributai Dėstytojo_Id ir Įstaigos_Id ir turime atributą Įstaigos_adresas, kuris funkciškai priklauso tik nuo atributo Įstaigos_Id. Kad lentelė atitiktų antros norminės formos reikalavimus perkelkime atributą Įstaigos_adresas į lentelę Įstaigos.

Trečioji norminė forma

Lentelė atitinka trečiąją normalinę formą kai joje nėra atributų, tranzityviai priklausančių nuo rakto.

Tarkime turime lentelę Dėstytojų_įstaigos={Id, Dėstytojo_Id, Įstaigos_Id, Įstaigos_adresas}

Joje raktą iškėlėme į lauką Id ir dabar Įstaigos_adresas nepriklauso nuo rakto dalies todėl lentelė atitinka 2NF, tačiau Įstaigos_adresas tranzityviai priklauso nuo Id t.y., Id->Įstaigos_Id->Įstaigos_adresas, todėl lentelė neatitinka 3NF.

Kad lentelė atitiktų 3NF formos reikalavimus perkelkime atributą Įstaigos_adresas į lentelę Įstaigos.

Boiso-Kodo norminė forma

Lentelė laikoma atitinkanti BKNF jei ji yra 1NF ir kiekvienos netrivialios FP determinantas ( FP kairioji dalis, Determinantas->Kažkas_tai ) yra viršraktis.

Tarkime turime lentelę L(X,Y,Z) kuri yra 1NF, lentelės viršranktis yra {X,Y}, funkckinės priklausomybės: {X,Y}->Z ir Z->Y. Kadangi Z->Y ir Z nėra viršraktis, tai lentelė L nėra BKNF.

Ketvirtoji norminė forma

Ketvirtosios norminės formos apibrėžime naudojama daugiareikšmės priklausomybės sąvoka A->->B. Lentelėje L(A,B,C) yra daugiareikšmė priklausomybė jei atributų B aibė, atitinkanti visas atributų A ir C reikšmes, priklauso nuo A, bet nepriklauso nuo C atributų reikšmių.

Lentelė yra 4NF tada, ir tik data, kai kiekviena netrivialiai daugiareikšmei priklausomybei A->->B, A yra lentelės L viršraktis.