Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Views, Materialized Views

Why use materialized views?
Same as virtual views, plus improve query performance

Virtual views
View V = ViewQuery(R1, R…, Rn)
Schema of V is schema of query result
Query Q involving V, conceptually:
V:= ViewQuery(R1, R…, Rn)
Evaluate Q
In reality, Q rewritten to use R1, R…, Rn instead of V
Note: Ri could itself be a view

Materialized views
View V = ViewQuery(R1, R…, Rn)
Create table V with schema of query result
Execute ViewQuery and put results in V
Queries refer to V as if its a table, because it is
V could be ginormous
Modification to R1, R2, R…, Rn -> recompute or modify V

Create Materialized View CA-CS As
Select C.cName, S.sName
From College C, Student S, Apply A
Where C.cName = A.cName and S.sID = A.sID and C.state = ‘CA’ and A.major = ‘CS’

Modifications to base data invalidate View

Good news: just update the stored table
Bad News: base tables must stay in sync
– same issues as virtual views

Picking which materialized views to create
Efficiency benefits of a materialized view depend on:
– size of data
– complexity of View- number of queries using view
– number of modifications affecting view
– also “incremental maintenance” versus full recomputation

December 3, 2011 | Comments Off on Intro to Databases: Views, Materialized Views | Categories: Posts | Permalink

Comments are closed.