View Maintenance

1
View Maintenance

• Based on several papers in view maintenance, most
  notably, A.Gupta and I.S.Mumick. Maintenance of
  Materialized Views Problems, Techniques, and
  Application. In Bulletin of Technical Committee
  on Data Engineering 1995
• Based on talk prepared by Katica Dimitrova and
  Aleksandar Icev

2
Outline

• Introduction to views
• The problem of materialized view maintenance
• The idea behind incremental view maintenance
• Dimensions the problem space
• View maintenance using full information
• The counting algorithm
• View maintenance using partial information
• Self-maintenance
• Applications of materialized views
• Open problems

3
Outline
?

• Introduction to views
• The problem of materialized view maintenance
• The idea behind incremental view maintenance
• Dimensions the problem space
• View maintenance using full information
• The counting algorithm
• View maintenance using partial information
• Self-maintenance
• Applications of incremental view maintenance
• Open problems

4
What is a view?
1. Introduction to views

• A view is a derived relation defined in terms of
  base (stored) relations.
• Example
• Flight is table of available direct flights.
• We need a view of flights with one intermediate
  stop.

CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), )
CREATE VIEW Conn(src, dest) AS SELECT F1.from,
F2.to FROM Flight F1, Flight F2 WHERE F1.to
F2.from
5
Views are treated as base tables in regard of
querying
1. Introduction to views
The view Conn
The base relation Flight
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
6
Motivation Why views?
1. Introduction to views

• Logical data independence
• If the conceptual schema changes, the changes
  can be masked through the views in the
  external schema
• Security
• Not everybody may see everything
• Relations tailored to users needs
• And many more reasons and applications
  discussed later

7
View materialization vs. computing on demand
1. Introduction to views
Have you seen something similar in this course?
(?)

• How will the following query be answered?
• Two options
• Computing on demand
• query modification, composing of the user query
  and the view query
• View materialization
• The view Conn would be materialized, its content
  would be stored in the database

SELECT FROM Conn C WHERE C.src Worcester
SELECT FROM (SELECT F1.from, F2.to FROM Flight
F1, Flight F2 WHERE F1.to F2.from ) AS
C WHERE C.src Worcester
8
View materialization vs. computing on demand
1. Introduction to views

• How will the following query be answered?
• Two options
• Computing on demand
• query modification, composing of the user query
  and the view query
• View materialization
• The view Conn would be materialized, its content
  would be stored in the database

SELECT FROM Conn C WHERE C.src Worcester
SELECT FROM (SELECT F1.from, F2.to FROM Flight
F1, Flight F2 WHERE F1.to F2.from ) AS
C WHERE C.src Worcester
9
View materialization vs. computing on demand
1. Introduction to views

• Trade-offs?

• Queries can be answered faster
• Indexes can be build over a materialized view to
  even more speed up the processing of the queries
  defined over the view
• The view requires additional storage space
• The consistency of the view has to be maintained

10
Outline
?

• Introduction to views
• The problem of materialized view maintenance
• The idea behind incremental view maintenance
• Dimensions the problem space
• View maintenance using full information
• The counting algorithm
• View maintenance using partial information
• Self-maintenance
• Applications
• Open problems

?
11
Definition of the problem of materialized view
maintenance
2. The problem of view maintenance

• What is materialized view maintenance ?
• When the base relations are modified,
• the view (often) becomes inconsistent.
• Updating the view to make it consistent
• is called view maintenance (refreshing).

12
View maintenance policies ( When ? )
2. The problem of view maintenance

• Immediate view maintenance
• The view is refreshed within the same
  transaction that updates the underlying tables
• the view is always up to date
• - slows down the transaction
• Deferred view maintenance
• Lazy the view is refreshed when query over it
  has to be evaluated
• - slows down the queries
• Periodic - the view is refreshed periodically,
  e.g., once a day
• Such views are called snapshots
• Forced the view is refreshed after a certain
  number of changes have been made to the
  underlying tables

13
Methods of view maintenance (How ?)
2. The problem of view maintenance

• Recomputation
• recompute to view from scratch
• Incremental view maintenance
• compute the changes to the view in response to
  the changes to the base relation
• add/delete some tuples in the existing
  materialized view
• Heuristics Incremental view maintenance is
  usually cheaper then recomputation

14
Outline
?

• Introduction to views
• The problem of materialized view maintenance
• The idea behind incremental view maintenance
• Dimensions the problem space
• View maintenance using full information
• The counting algorithm
• View maintenance using partial information
• Self-maintenance
• Applications
• Open problems

?
?
15
The idea behind incremental view maintenance

• Example Flight is table of available direct
  flights. We need a view of flights with one
  intermediate stop

16
3. The idea behind view maintenance
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
F1
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
The view Conn
F2
The base relation
17
3. The idea behind view maintenance
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F1
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
Seattle Las Vegas
Boston New York
Philadelphia New York
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
Inserted tuple
F2
The view Conn
New tuples in the view
The base relation
18
3. The idea behind view maintenance
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F1
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
Seattle Las Vegas
Boston New York
Philadelphia New York
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
Inserted tuple
F2
The view Conn
New tuples in the view
The base relation
19
3. The idea behind view maintenance
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F1
F1
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
Seattle Las Vegas
Boston New York
Philadelphia New York
? F1
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F1?F1? F1
F2
The view Conn
The base relation
20
The differentiation equation
3. The idea behind view maintenance
21
3. The idea behind view maintenance
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F1
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
Seattle Las Vegas
Boston New York
Philadelphia New York
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F2
The view Conn
The base relation
22
3. The idea behind view maintenance
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F1
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
Seattle Las Vegas
Boston New York
Philadelphia New York
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
Join
F2
The view Conn
The base relation
23
3. The idea behind view maintenance
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F1
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
Seattle Las Vegas
Boston New York
Philadelphia New York
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
Join
F2
The view Conn
The base relation
24
3. The idea behind view maintenance
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F1
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
Seattle Las Vegas
Boston New York
Philadelphia New York
Join?
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F2
The view Conn
The base relation
25
3. The idea behind view maintenance
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
F1
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
Seattle Las Vegas
Boston New York
Philadelphia New York
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Seattle New York
Savings!
F2
The view Conn
The base relation
26
3. The idea behind view maintenance

• Insertions
• The observed example was for insertions into the
  base relation
• Deletions
• If tuples are deleted from a base relation, the
  tuples that need to be deleted from the view are
  computed similarly using deltas
• Updates
• May be treated separately or may be modeled as
  deletions followed by inserts
• Multiple relations involved
• The deltas are similarly computed

27
Outline
?

• Introduction to views
• The problem of materialized view maintenance
• The idea behind incremental view maintenance
• Dimensions the problem space
• View maintenance using full information
• The counting algorithm
• View maintenance using partial information
• Self-maintenance
• Applications
• Open problems

?
?
?
28
Dimensions

• Information dimension
• The information available for view maintenance
  (other than the view definition and the
  modification, which are always assumed available)
• Base relations, the materialized view, other
  views, integrity constraints
• Dont we have all these information?
• Sometimes we dont, sometimes it is expensive to
  access them (the base relations for example when
  views are physically not close to the data)
• Modification dimension
• Which modifications are allowed (handled)
• Insertions, deletions, updates, sets of
  modifications, changes to the view definition,
  changes to the base relations definition

29
Dimensions information modification
4. Dimensions

• Example

CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20) )
CREATE VIEW CheapF(src, dest) AS SELECT
DISTINCT F.from, F.to FROM Flight F WHERE
F.price lt 400
Information Modification The materialized view Conn only The base relation Flight only Key info only (from, to) is key in Flight
Insert
Delete
Update
30
Dimensions information modification
4. Dimensions
Can we maintain the view when tuples are
inserted into the base relation if the only
information available is the materialized view
(its content) Reminder The view definition and
the modification are always availabe

• Example

CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20) )
CREATE VIEW CheapF(src, dest) AS SELECT
DISTINCT F.from, F.to FROM Flight F WHERE
F.price lt 400
Information Modification The materialized view Conn only The base relation Flight only Key info only (from, to) is key in Flight
Insert
Delete
Update
31
Dimensions information modification
4. Dimensions

• Example

CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20) )
CREATE VIEW CheapF(src, dest) AS SELECT
DISTINCT F.from, F.to FROM Flight F WHERE
F.price lt 400
Information Modification The materialized view Conn only The base relation Flight only Key info only (from, to) is key in Flight
Insert ? ? ?
Delete ? ? ?
Update ? ? ?
32
Dimensions
4. Dimensions

• Language dimension
• The expressiveness of the View definition
  language allowed
• Select-Project-Join (SPJ), union, aggregation,
  negation
• Recursion what does it mean?
• Views that use are defined of terms of themselves
• Example Create a view of all the possible
  flights with arbitrary connections-stops.
  
• 
  (only for illustration, not real SQL!!!)

CREATE VIEW Conn(src, dest) AS SELECT F1.from,
F1.to FROM Flight F1 UNION SELECT
C.src, F2.to FROM Conn C, Flight F2 WHERE
C.destF2.from
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), )
33
Dimensions
4. Dimensions

• Instance dimension
• Does it work for all
• Database instances
• Modification instances

34
Dimensions
4. Dimensions
Instance dimension
35
Outline
?

• Introduction to views
• The problem of materialized view maintenance
• The idea behind incremental view maintenance
• Dimensions the problem space
• View maintenance using full information
• The counting algorithm
• View maintenance using partial information
• Self-maintenance
• Applications
• Open problems

?
?
?
?
36
View maintenance using full information

• Classical view maintenance algorithms assume
  
• Full Information the base relations, the
  materialized view, keys,
• All database and modification instances
• Modification inserts, deletes, updates (maybe
  as insert-delete)
• Language Focus on efficient techniques for
  maintaining views expressed in different subset
  of the view definition language
• Classification along the language dimension
• Nonrecursive views
• The counting algorithm
• Outer-join views
• Recursive views

37
The Counting Algorithm - Motivation
5. View Main. using full information
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
F1
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
F2
The view Conn
The base relation
38
The Counting Algorithm - Motivation
5. View Main. using full information
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Shall we delete the (Worcester, Seattle) tuple
from the view?
F1
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
F2
The view Conn
The base relation
39
The Counting Algorithm - Motivation
5. View Main. using full information
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
No, because it can still be derived from the
remaining tuples
F1
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Src Dest
Worcester New York
Worcester Seattle
Boston Las Vegas
F2
The view Conn
The base relation
40
The Counting Algorithm
5. View Main. using full information

• We need to know if there are more derivations of
  one tuple in the view
• Main idea
• Keep a count of the number of derivations for
  each tuple in the view
• A tuple is removed from the view only if its
  count is zero

41
The
Counting Algorithm
5. View Main. using full information
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
F1
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Src Dest Count
Worcester New York 1
Worcester Seattle 2
Boston Las Vegas 1
F2
The view Conn
The base relation
42
The
Counting Algorithm
5. View Main. using full information
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
F1
From To
Worcester Boston
Boston New York
Boston Seattle
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Src Dest Count
Worcester New York 1
Worcester Seattle 2 1
Boston Las Vegas 1
F2
The view Conn
The base relation
43
The
Counting Algorithm
5. View Main. using full information
From To
Worcester Boston
Boston New York
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
F1
From To
Worcester Boston
Boston New York
Worcester Philadelphia
New York Las Vegas
Philadelphia Seattle
Src Dest Count
Worcester New York 1
Worcester Seattle 1 0
Boston Las Vegas 1
F2
The view Conn
The base relation
44
The Counting Algorithm
5. View Main. using full information

• What if we have aggregation?

CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL )
CREATE VIEW CheapFlight(src, dest, minPrice) AS
SELECT F.from, F.to, MIN (F.price) FROM
Flight F GROUP BY F.from, F.to
45
The
Counting Algorithm - Aggregation
5. View Main. using full information
From To Price
Boston New York 200
Chicago Philadelphia 350
Boston New York 300
New York Las Vegas 330
Chicago Philadelphia 490
Boston New York 250
The inserted tuple does not affect the view
Inserted tuple
The base relation
Src Dest MinPrice Count
Boston New York 200 1
Chicago Philadelphia 350 1
New York Las Vegas 330 1
Potentially affected tuple
The view CheapFlight
46
The
Counting Algorithm - Aggregation
5. View Main. using full information
From To Price
Boston New York 200
Chicago Philadelphia 350
Boston New York 300
New York Las Vegas 330
Chicago Philadelphia 490
Boston New York 180
One tuple in the view has to be updated
Inserted tuple
The base relation
Src Dest MinPrice Count
Boston New York 200 180 1
Chicago Philadelphia 350 1
New York Las Vegas 330 1
Potentially affected tuple
The view CheapFlight
47
The
Counting Algorithm - Aggregation
5. View Main. using full information
From To Price
Boston New York 200
Chicago Philadelphia 350
Boston New York 300
New York Las Vegas 330
Chicago Philadelphia 490
Boston New York 180
One tuple in the view has to be recomputed
Deleted tuple
The base relation
Src Dest MinPrice Count
Boston New York 180 200 1
Chicago Philadelphia 350 1
New York Las Vegas 330 1
Potentially affected tuple
The view CheapFlight
48
The Counting Algorithm - Aggregation
5. View Main. using full information

• When a change to the base relation occurs
• Identifies the tuples that may be affected
• Whenever possible incrementally computes new
  values of affected tuples by only looking at
  materialized view and modification.
• Other aggregation functions that may be computed
  this way COUNT, SUM, MIN, MAX
• Some other aggregation functions like AVERAGE and
  VARIANCE can be decomposed into incrementally
  computable functions

49
The Counting Algorithm Multiple relations and
views over views
5. View Main. using full information

• Handles views over multiple relations, handles
  views over views (by first updating the views
  lower in the hierarchy)

Materialized view 2
I I
I
Materialized view 1
Base relation 1
Base relation 2
Base relation 3
50
The Counting Algorithm - Summary
5. View Main. using full information

• Keeps track of the number of derivation of each
  tuple tuples with count zero are deleted from
  the view
• Handles updates as difference of positive and
  negative counts
• Handles views over multiple relations, handles
  views over views
• Language limitations SPJ views, UNION,
  negation, aggregation
• Works for both set and duplicate semantics

51
Outline
?

• Introduction to views
• The problem of materialized view maintenance
• The idea behind incremental view maintenance
• Dimensions the problem space
• View maintenance using full information
• The counting algorithm
• View maintenance using partial information
• Self-maintenance
• Applications
• Open problems

?
?
?
?
?
52
View maintenance using partial information

• Views may be maintainable using partial
  information
• May depend on the modification insert, delete or
  update
• Goals
• Check whether the view can be maintained
• How to maintain the view

53
Using no Information Query Independent of Update
6. View Main. using partial information

• Some modifications to the base tables may be
  irrelevant to the view - leave it unchanged
• Determine if the modification is irrelevant
  using
• The view definition
• The modification
• Recognizing irrelevant modifications prevents
  unnecessary delta-computations

54
Query Independent of Update - Example
6. View Main. using partial information
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20)
) CREATE TABLE RCompany ( name CHAR(20),
rating INTEGER )
CREATE VIEW GoodFlights(src, dest) AS SELECT
F.from, F.to FROM Flight F, RCompany C WHERE
F.company C.name AND C.rating gt 5 AND
F.price lt 400

• Which of these modifications would be irrelevant?
  

INSERT INTO RCompany VALUES (Swissair, 10)
INSERT INTO RCompany VALUES (EnronAir, 2)
DELETE FROM Flight F WHERE F.fromBoston AND PRICElt 350
55
Query Independent of Update - Example
6. View Main. using partial information
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20)
) CREATE TABLE RCompany ( name CHAR(20),
rating INTEGER )
CREATE VIEW GoodFlights(src, dest) AS SELECT
F.from, F.to FROM Flight F, RCompany C WHERE
F.company C.name AND C.rating gt 5 AND
F.price lt 400

• Which of these modifications would be irrelevant?
  

INSERT INTO RCompany VALUES (Swissair, 10) ?
INSERT INTO RCompany VALUES (EnronAir, 2) ?
DELETE FROM Flight F WHERE F.fromBoston AND PRICElt 350 ?
56
Self-Maintenance
6. View Main. using partial information

• Self-maintainable views are views that can be
  maintained using only materialized view (self)
  and key constraints
• A view may be self-maintainable in respect to
  some modification types (insert, delete, update)
  but not in respect to
  all of them

57
Self-Maintenance
6. View Main. using partial information

• Distinguished Attribute Appears in the SELECT
  clause in the view definition
• Exposed Attribute Used in a predicate in the
  view definition

Distinguished
Exposed
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20),
duration REAL, PRIMARY KEY (from, to)
) CREATE TABLE RCompany ( name CHAR(20),
rating INTEGER PRIMARY KEY (name) )
CREATE VIEW GoodFlights(src, dest) AS SELECT
F.from, F.to FROM Flight F, RCompany C WHERE
F.company C.name AND C.rating gt 5 AND
F.price lt 400
58
Self-Maintenance - insert
6. View Main. using partial information

• SPJ view that takes join of two or more distinct
  relations is not self maintainable in respect to
  insertions

Distinguished
Exposed
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20),
duration REAL, PRIMARY KEY (from, to)
) CREATE TABLE RCompany ( name CHAR(20),
rating INTEGER PRIMARY KEY (name) )
CREATE VIEW GoodFlights(src, dest) AS SELECT
F.from, F.to FROM Flight F, RCompany C WHERE
F.company C.name AND C.rating gt 5 AND
F.price lt 400
59
Self-Maintenance - update
6. View Main. using partial information

• SPJ view is self-maintainable in respect to
  updates on non-exposed attributes when the key
  attributes are distinguished.

Distinguished
Exposed
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20),
duration REAL, PRIMARY KEY (from, to)
) CREATE TABLE RCompany ( name CHAR(20),
rating INTEGER PRIMARY KEY (name) )
CREATE VIEW GoodFlights(src, dest) AS SELECT
F.from, F.to FROM Flight F, RCompany C WHERE
F.company C.name AND C.rating gt 5 AND
F.price lt 400
60
Self-Maintenance - update
6. View Main. using partial information
Update of duration does not affect the view
Distinguished
Exposed
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20),
duration REAL, PRIMARY KEY (from, to)
) CREATE TABLE RCompany ( name CHAR(20),
rating INTEGER PRIMARY KEY (name) )
CREATE VIEW GoodFlights(src, dest) AS SELECT
F.from, F.to FROM Flight F, RCompany C WHERE
F.company C.name AND C.rating gt 5 AND
F.price lt 400
61
Partial-referenceUsing Materialized View and
Some Base Relations
6. View Main. using partial information

• Only a subset of the base relations and the
  materialized view are available
• Cases
• Modified relation is not available
• Chronicle views (views over an ordered sequence
  of tuples with insertions being the only
  permissible modification) continuous queries
• Only the modified relation and the view are
  available
• Instance specific partial-reference maintenance
• Some algorithms successfully maintain a view for
  some instances of the database and modification
  but not for others

62
Outline
?

• Introduction to views
• The problem of materialized view maintenance
• The idea behind incremental view maintenance
• Dimensions the problem space
• View maintenance using full information
• The counting algorithm
• View maintenance using partial information
• Self-maintenance
• Applications
• Open problems

?
?
?
?
?
?
63
Applications of materialized views

• Query speed-up
• Integrity constraint checking
• Query optimization
• Data warehousing
• Chronicle systems ( continuous queries)
• Mobile systems
• Data visualization

64
Query speed-up
7. Applications

• Queries are answered faster if their answers are
  precomputed (materialized)
• How does the keeping of precomputed query result
  (materialized view) correlate to the
• Frequency of the query ?
• Frequency of the updates to the base relations?

65
Integrity constraint checking
7. Applications

• Static integrity constraints can be modeled as
  materialized views that are required to be empty
• Example

CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT F.company FROM Flight
F GROUP BY F.company HAVING COUNT() gt 100 )
)
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20) )
66
Integrity constraint checking
7. Applications

• Example

CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT F.company FROM Flight
F GROUP BY F.company HAVING COUNT() gt 100 )
)
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20) )
Can be modeled as
CREATE VIEW Monopolies AS SELECT
F.company FROM Flight F GROUP BY
F.company HAVING COUNT() gt 100
CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT FROM Monopolies ) )
67
Integrity constraint checking
7. Applications

• Example

What happens on updates to the base relation?
CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT F.company FROM Flight
F GROUP BY F.company HAVING COUNT() gt 100 )
)
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20) )
CREATE VIEW Monopolies AS SELECT
F.company FROM Flight F GROUP BY
F.company HAVING COUNT() gt 100
CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT FROM Monopolies ) )
68
Integrity constraint checking
7. Applications

• Example

CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT F.company FROM Flight
F GROUP BY F.company HAVING COUNT() gt 100 )
)
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20) )
How can we do better?
CREATE VIEW Monopolies AS SELECT
F.company FROM Flight F GROUP BY
F.company HAVING COUNT() gt 100
CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT FROM Monopolies ) )
69
Integrity constraint checking
7. Applications

• Example

Which additional view shall we materialize?
CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT F.company FROM Flight
F GROUP BY F.company HAVING COUNT() gt 100 )
)
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20) )
CREATE VIEW Monopolies AS SELECT
F.company FROM Flight F GROUP BY
F.company HAVING COUNT() gt 100
CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT FROM Monopolies ) )
70
Integrity constraint checking
7. Applications
CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT F.company FROM Flight
F GROUP BY F.company HAVING COUNT() gt 100 )
)
CREATE TABLE Flight ( from CHAR(20), to
CHAR(20), price REAL, company CHAR(20) )
CREATE VIEW Monopolies (name) AS SELECT
F.company FROM Flight F GROUP BY
F.company HAVING COUNT() gt 100
CREATE ASSERTION NoMonopoly CHECK ( NOT EXISTS
( SELECT FROM Monopolies ) )

• Materialize an additional view for maintaining
  Monopolies

CREATE VIEW Helper (name, total) AS SELECT
F.company, COUNT() FROM Flight F
GROUP BY F.company
CREATE VIEW Monopolies(name) AS SELECT
H.company FROM Helper H WHERE H.total
gt 100
71
Query optimization
7. Applications

• Materialized views can be used even for answering
  queries that do not explicitly contain the views
  in their definition
• Cashed results can be seen as temporarily
  materialized views and can also by used for
  faster query optimization
• Problems
• Recognizing which views may be utilized for
  processing the query
• Finding which views and/or relations should be
  used for achieving lowest cost of the query
  evaluation

72
Query optimization the Microsoft SQL Server
approach
7. Applications

• Approach
• Generate all possible rewritings of the query
• View matching Determine subexpressions that may
  be computed from materialized views
• Estimate their costs
• Choose the one with the lowest cost
• Indexes the view definitions using a special
  index to speed up the view matching
• Language limitation Select-Project-Join-GroupBy

73
Outline
?

• Introduction to views
• The problem of materialized view maintenance
• The idea behind incremental view maintenance
• Dimensions the problem space
• View maintenance using full information
• The counting algorithm
• View maintenance using partial information
• Self-maintenance
• Applications
• Open problems

?
?
?
?
?
?
?
74
Open problems
7. Open problems
Instance dimension
75
Open problems
7. Open problems

• Great portion of the problem space is still
  unsolved or may be solved more efficiently , so
  many open questions remain
• Other issues
• When to perform the maintenance?
• How to efficiently select additional views to be
  maintained?
• ..

76
Outline
?

• Introduction to views
• The problem of materialized view maintenance
• The idea behind incremental view maintenance
• Dimensions the problem space
• View maintenance using full information
• The counting algorithm
• View maintenance using partial information
• Self-maintenance
• Applications
• Open problems

?
?
?
?
?
?
?
77
References

• A.Gupta, I.S.Mumick. Maintenance of Materialized
  Views Problems, Techniques, and Application. In
  Bulletin of the Technical Committee on Data
  engineering 1995
• Most of this presentation
• R.Ramakrishnan, J.Gehrke. Database Management
  Systems, McGraw-Hill 2000
• Introduction to views
• A.Gupta, I.S. Mumick, V.S. Subrahmanian.
  Maintaining Views Incrementally. In SIGMOD 1995
• The counting algorithm
• J.A. Blakeley, P.Larson, and F.Tompa.
  Efficiently Updating Materialized Views. In
  SIGMOD 1986
• Query independent of update
• K.Ross, D.Srivastava, S.Sudarshan. Materialized
  View Maintenance and Integrity Constraint
  Checking Trading Space for Time. In SIGMOD 96.
• Applications Integrity constraint checking
• J Goldstein, P. Larson. Optimizing Queries Using
  Materialized Views A practical Scalable
  Solution. In SIGMOD 2001
• Applications Query optimization the Microsoft
  SQL Server approach