Title: View%20Maintenance
1View 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
2Outline
- 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
3Outline
?
- 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
4What 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
5Views 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
6Motivation 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
7View 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
8View 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
9View materialization vs. computing on demand
1. Introduction to views
- 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
10Outline
?
- 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
?
11Definition 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).
12View 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
13Methods 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
14Outline
?
- 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
?
?
15The idea behind incremental view maintenance
- Example Flight is table of available direct
flights. We need a view of flights with one
intermediate stop
163. 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
173. 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
183. 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
193. 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
20The differentiation equation
3. The idea behind view maintenance
213. 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
223. 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
233. 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
243. 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
253. 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
263. 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
27Outline
?
- 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
?
?
?
28Dimensions
- 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
29Dimensions information modification
4. Dimensions
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
30Dimensions 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
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
31Dimensions information modification
4. Dimensions
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 ? ? ?
32Dimensions
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), )
33Dimensions
4. Dimensions
- Instance dimension
- Does it work for all
- Database instances
- Modification instances
34Dimensions
4. Dimensions
Instance dimension
35Outline
?
- 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
?
?
?
?
36View 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
37The 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
38The 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
39The 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
40The 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
44The 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
48The 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
49The 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
50The 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
51Outline
?
- 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
?
?
?
?
?
52View 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
53Using 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
54Query 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
55Query 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 ?
56Self-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
57Self-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
58Self-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
59Self-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
60Self-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
61Partial-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
62Outline
?
- 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
?
?
?
?
?
?
63Applications of materialized views
- Query speed-up
- Integrity constraint checking
- Query optimization
- Data warehousing
- Chronicle systems ( continuous queries)
- Mobile systems
- Data visualization
64Query 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
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
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
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
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
71Query 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
72Query 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
73Outline
?
- 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
?
?
?
?
?
?
?
74Open problems
7. Open problems
Instance dimension
75Open 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? - ..
76Outline
?
- 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
?
?
?
?
?
?
?
77References
- 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