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
BUT…
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

Modifications
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