View Maintenance Based on several papers in view
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 1
Outline 1. Introduction to views 2. The problem of materialized view maintenance 3. The idea behind incremental view maintenance 4. Dimensions – the problem space 5. View maintenance using full information § 6. The counting algorithm View maintenance using partial information § Self-maintenance 7. Applications of materialized views 8. Open problems 11/1/2020 2
Outline 1. Introduction to views 2. The problem of materialized view maintenance 3. The idea behind incremental view maintenance 4. Dimensions – the problem space 5. View maintenance using full information § 6. The counting algorithm View maintenance using partial information § Self-maintenance 7. Applications of incremental view maintenance 8. Open problems 11/1/2020 3
1. Introduction to views What is a view? Ø 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 F 1. from, F 2. to FROM Flight F 1, Flight F 2 WHERE F 1. to = F 2. from; 11/1/2020 4
1. Introduction to views Views are treated as base tables in regard of querying The base relation Flight From Worcester Boston Worcester New York Philadelphia To Boston New York Seattle Philadelphia Las Vegas Seattle The view Conn Src Dest Worcester New York Worcester Seattle Boston Las Vegas From Worcester To Boston Worcester New York Philadelphia New York Seattle Philadelphia Las Vegas Seattle 11/1/2020 5
1. Introduction to views Motivation: Why 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 user’s needs Ø And many more reasons and applications … discussed later 11/1/2020 6
1. Introduction to views View materialization vs. computing on demand Ø How will the following query be answered? SELECT * FROM Conn C WHERE C. src = “Worcester” Ø Two options: § Computing on demand • query modification, composing of the user query and the view query SELECT * FROM (SELECT F 1. from, F 2. to FROM Flight F 1, Flight F 2 WHERE F 1. to = F 2. from ) AS C WHERE C. src = “Worcester” § View materialization • The 11/1/2020 view Conn would be materialized, its content would be stored in the database 7
1. Introduction to views View materialization vs. computing on demand Ø How will the following query be answered? SELECT * FROM Conn C WHERE C. src = “Worcester” Ø Two options: § Computing on demand • query modification, composing of the user query and the view query SELECT * FROM (SELECT F 1. from, F 2. to FROM Flight F 1, Flight F 2 WHERE F 1. to = F 2. from ) AS C WHERE C. src = “Worcester” § View materialization • The 11/1/2020 view Conn would be materialized, its content would be stored in the database 8
1. Introduction to views View materialization vs. computing on demand Ø 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 11/1/2020 9
Outline 1. Introduction to views 2. The problem of materialized view maintenance 3. The idea behind incremental view maintenance 4. Dimensions – the problem space 5. View maintenance using full information § 6. The counting algorithm View maintenance using partial information § Self-maintenance 7. Applications 8. Open problems 11/1/2020 10
2. The problem of view maintenance Definition of the problem of materialized 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). 11/1/2020 11
2. The problem of view maintenance View maintenance policies ( When ? ) Ø 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 11/1/2020 – the view is refreshed after a certain number of changes have been made to the underlying tables 12
2. The problem of view maintenance Methods of view maintenance (How ? ) Ø 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 11/1/2020 13
Outline 1. Introduction to views 2. The problem of materialized view maintenance 3. The idea behind incremental view maintenance 4. Dimensions – the problem space 5. View maintenance using full information § 6. The counting algorithm View maintenance using partial information § Self-maintenance 7. Applications 8. Open problems 11/1/2020 14
The idea behind incremental view maintenance Example: Flight is table of available direct flights. We need a view of flights with one intermediate stop 11/1/2020 15
3. The idea behind view maintenance F 1 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas Philadelphia Seattle F 2 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas Src Dest Worcester New York Worcester Seattle Boston Las Vegas The view Conn Philadelphia Seattle 11/1/2020 The base relation 16
F 1 F 2 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas Philadelphia Seattle New York 11/1/2020 The base relation 3. The idea behind view maintenance Inserted tuple Src Dest Worcester New York Worcester Seattle Boston Las Vegas Seattle Las Vegas Boston New York Philadelphia New York The view Conn New tuples in the view 17
F 1 F 2 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas Philadelphia Seattle New York 11/1/2020 The base relation 3. The idea behind view maintenance Inserted tuple Src Dest Worcester New York Worcester Seattle Boston Las Vegas Seattle Las Vegas Boston New York Philadelphia New York The view Conn New tuples in the view 18
F 1 F 2 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas Philadelphia Seattle New York From To Worcester Boston New York Boston 3. The idea behind view maintenance F 1 Src Dest Worcester New York Worcester Seattle Boston Las Vegas Seattle Boston New York Worcester Philadelphia New York Las Vegas Philadelphia Seattle New York F 1 =F 1+ F 1 The view Conn 11/1/2020 The base relation 19
3. The idea behind view maintenance The differentiation equation 11/1/2020 20
F 1 F 2 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas Philadelphia Seattle New York 3. The idea behind view maintenance Src Dest Worcester New York Worcester Seattle Boston Las Vegas Seattle Las Vegas Boston New York Philadelphia New York The view Conn 11/1/2020 The base relation 21
F 1 F 2 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas Philadelphia Seattle New York 3. The idea behind view maintenance Join Src Dest Worcester New York Worcester Seattle Boston Las Vegas Seattle Las Vegas Boston New York Philadelphia New York The view Conn 11/1/2020 The base relation 22
F 1 F 2 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas Philadelphia Seattle New York 3. The idea behind view maintenance Join Src Dest Worcester New York Worcester Seattle Boston Las Vegas Seattle Las Vegas Boston New York Philadelphia New York The view Conn 11/1/2020 The base relation 23
F 1 F 2 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas Philadelphia Seattle New York 3. The idea behind view maintenance Join= Src Dest Worcester New York Worcester Seattle Boston Las Vegas Seattle Las Vegas Boston New York Philadelphia New York The view Conn 11/1/2020 The base relation 24
F 1 F 2 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas Philadelphia Seattle New York 3. The idea behind view maintenance Savings! Src Dest Worcester New York Worcester Seattle Boston Las Vegas Seattle Las Vegas Boston New York Philadelphia New York The view Conn 11/1/2020 The base relation 25
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 delta’s Ø Updates § May be treated separately or may be modeled as deletions followed by inserts Ø Multiple relations involved § 11/1/2020 The deltas are similarly computed 26
Outline 1. Introduction to views 2. The problem of materialized view maintenance 3. The idea behind incremental view maintenance 4. Dimensions – the problem space 5. View maintenance using full information § 6. The counting algorithm View maintenance using partial information § Self-maintenance 7. Applications 8. Open problems 11/1/2020 27
4. Dimensions Expressiveness of View Definition Language Amount of Information. . . Integrity Constraints …. Recursion Difference Outer-Joins Chronicle Algebra Union Subqueries Aggregation Arithmetic Duplicates Other Views Base Relations Materialized View Insertions Deletions 11/1/2020 Conjunctive Queries Updates Sets of each + Instance dimension Type Group Updates Change view definition of Modification 28
Information Dimension Ø Information dimension § Information available for view maintenance (other than view definition and the modification, which are always assumed available) § E. g. , • • base relations, the materialized view, other views, integrity constraints… § Don’t we have all these information? • Sometimes we don’t, • Sometimes it is expensive to access them (base relations when views are physically not close to data) 11/1/2020 29
4. Dimensions – information & modification Ø Example: CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL, company CHAR(20) ); Information Modification CREATE VIEW Cheap. F(src, dest) AS SELECT DISTINCT F. from, F. to FROM Flight F WHERE F. price < 400; Materialized view Conn only Plus, base relation Flight Key info only: (from, to) is key in Flight Insert Delete 11/1/2020 Update 30
4. Dimensions – information & modification Can we maintain the view when tuples are inserted into base relation if only information available is the Ø Example: materialized view (its content) CREATE TABLE Flight CREATE VIEW Cheap. F(src, dest) AS ( from CHAR(20), Reminder: The view definition and the modification SELECT DISTINCT F. from, F. to to CHAR(20), are always available FROM Flight F price REAL, WHERE F. price < 400; company CHAR(20) ); Information Modification The materialized view Conn only The base relation Flight only Key info only: (from, to) is key in Flight Insert Delete 11/1/2020 Update 31
4. Dimensions – information & modification Ø Example: CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL, company CHAR(20) ); Information Modification Insert Delete 11/1/2020 Update CREATE VIEW Cheap. F(src, dest) AS SELECT DISTINCT F. from, F. to FROM Flight F WHERE F. price < 400; The materialized view Conn only The base relation Flight only Key info only: (from, to) is key in Flight 32
Outline 1. Introduction to views 2. The problem of materialized view maintenance 3. The idea behind incremental view maintenance 4. Dimensions – the problem space 5. View maintenance using full information § 6. The counting algorithm View maintenance using partial information § Self-maintenance 7. Applications 8. Open problems 11/1/2020 33
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 ( as insert-delete ) Language: Focus on efficient techniques for maintaining PSJ views expressed in subset of view definition language (SQL) Classification along language dimension: § Nonrecursive views • The counting algorithm § § § Outer-join views Recursive views … 11/1/2020 34
5. View Main. using full information F 1 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas The Counting Algorithm - Motivation Philadelphia Seattle F 2 From To Worcester Boston New York Boston Src Dest Worcester New York Seattle Worcester Philadelphia Boston Las Vegas New York Las Vegas Philadelphia Seattle The view Conn 11/1/2020 The base relation 35
5. View Main. using full information F 1 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas The Counting Algorithm - Motivation Shall we delete the (Worcester, Seattle) tuple from the view? Philadelphia Seattle F 2 From To Worcester Boston New York Boston Src Dest Worcester New York Seattle Worcester Philadelphia Boston Las Vegas New York Las Vegas Philadelphia Seattle The view Conn 11/1/2020 The base relation 36
5. View Main. using full information F 1 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas The Counting Algorithm - Motivation No, because it can still be derived from the remaining tuples Philadelphia Seattle F 2 From To Worcester Boston New York Boston Src Dest Worcester New York Seattle Worcester Philadelphia Boston Las Vegas New York Las Vegas Philadelphia Seattle The view Conn 11/1/2020 The base relation 37
5. View Main. using full information The Counting Algorithm Ø We need to know if there are more derivations of one tuple in the view Ø Main idea: § Keep count of number of derivations for each tuple in view § A tuple is removed from view only if its count is zero 11/1/2020 38
5. View Main. using full information F 1 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas The Counting Algorithm Philadelphia Seattle F 2 From To Worcester Boston Src Dest Count Boston New York Worcester New York 1 Boston Seattle Worcester Seattle 2 Worcester Philadelphia Boston Las Vegas 1 New York Las Vegas Philadelphia Seattle The view Conn 11/1/2020 The base relation 39
5. View Main. using full information F 1 From To Worcester Boston New York Boston Seattle Worcester Philadelphia New York Las Vegas The Counting Algorithm Philadelphia Seattle F 2 From To Worcester Boston Src Dest Count Boston New York Worcester New York 1 Boston Seattle Worcester Seattle 2 Worcester Philadelphia Boston Las Vegas 1 New York Las Vegas Philadelphia Seattle 1 The view Conn 11/1/2020 The base relation 40
5. View Main. using full information F 1 From To Worcester Boston New York Worcester Philadelphia New York Las Vegas The Counting Algorithm Philadelphia Seattle F 2 From To Worcester Boston Src Dest Count Boston New York Worcester New York 1 Worcester Philadelphia Worcester Seattle 1 New York Las Vegas Boston Las Vegas 1 Philadelphia Seattle 0 The view Conn 11/1/2020 The base relation 41
5. View Main. using full information The Counting Algorithm Ø What if we have aggregation? CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL ); CREATE VIEW Cheap. Flight(src, dest, min. Price) AS SELECT F. from, F. to, MIN (F. price) FROM Flight F GROUP BY F. from, F. to; 11/1/2020 42
5. View Main. using full information The Counting Algorithm - Aggregation 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 Min. Price Count Boston New York 200 1 Chicago Philadelphia 350 1 New York Las Vegas 330 1 11/1/2020 Potentially affected tuple The view Cheap. Flight 43
5. View Main. using full information The Counting Algorithm - Aggregation 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 Min. Price Count Boston New York 200 180 1 Chicago Philadelphia 350 1 New York Las Vegas 330 1 11/1/2020 Potentially affected tuple The view Cheap. Flight 44
5. View Main. using full information The Counting Algorithm - Aggregation 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 Min. Price Count Boston New York 180 200 1 Chicago Philadelphia 350 1 New York Las Vegas 330 1 11/1/2020 Potentially affected tuple The view Cheap. Flight 45
5. View Main. using full information The Counting Algorithm - Aggregation Ø 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 11/1/2020 can be decomposed into incrementally computable functions 46
5. View Main. using full information The Counting Algorithm – Multiple relations and views over views Ø Handles views over multiple relations, handles views over views (by first updating the views lower in the hierarchy) Materialized view 2 II I Materialized view 1 11/1/2020 Base relation 1 Base relation 2 Base relation 3 47
5. View Main. using full information The Counting Algorithm - Summary Ø 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 11/1/2020 48
Outline 1. Introduction to views 2. The problem of materialized view maintenance 3. The idea behind incremental view maintenance 4. Dimensions – the problem space 5. View maintenance using full information § 6. The counting algorithm View maintenance using partial information § Self-maintenance 7. Applications 8. Open problems 11/1/2020 49
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 11/1/2020 50
6. View Main. using partial information Using no Information: Query Independent of Update Ø 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 11/1/2020 51
6. View Main. using partial information Query Independent of Update - Example CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL, company CHAR(20) ) CREATE TABLE RCompany ( name CHAR(20), rating INTEGER ) Ø CREATE VIEW Good. Flights(src, dest) AS SELECT F. from, F. to FROM Flight F, RCompany C WHERE F. company = C. name AND C. rating > 5 AND F. price < 400 Which of these modifications would be irrelevant? INSERT INTO RCompany VALUES (“Swissair”, 10) INSERT INTO RCompany VALUES (“Enron. Air”, 2) DELETE FROM Flight F WHERE F. from=“Boston” AND PRICE< 350 11/1/2020 52
6. View Main. using partial information Query Independent of Update - Example CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL, company CHAR(20) ) CREATE TABLE RCompany ( name CHAR(20), rating INTEGER ) Ø CREATE VIEW Good. Flights(src, dest) AS SELECT F. from, F. to FROM Flight F, RCompany C WHERE F. company = C. name AND C. rating > 5 AND F. price < 400 Which of these modifications would be irrelevant? INSERT INTO RCompany VALUES (“Swissair”, 10) INSERT INTO RCompany VALUES (“Enron. Air”, 2) DELETE FROM Flight F WHERE F. from=“Boston” AND PRICE< 350 11/1/2020 53
6. View Main. using partial information Self-Maintenance Ø 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 11/1/2020 54
6. View Main. using partial information Self-Maintenance Distinguished Attribute – Appears in the SELECT clause in the view definition Ø Exposed Attribute – Used in a predicate in the view definition Ø 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 11/1/2020 CHAR(20), rating INTEGER PRIMARY KEY (name) ) Distinguished CREATE VIEW Good. Flights(src, dest) AS SELECT F. from, F. to FROM Flight F, RCompany C WHERE F. company = C. name AND C. rating > 5 AND F. price < 400 55
6. View Main. using partial information Self-Maintenance - insert Ø SPJ view that takes join of two or more distinct relations is not self maintainable in respect to insertions 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 11/1/2020 CHAR(20), rating INTEGER PRIMARY KEY (name) ) Distinguished CREATE VIEW Good. Flights(src, dest) AS SELECT F. from, F. to FROM Flight F, RCompany C WHERE F. company = C. name AND C. rating > 5 AND F. price < 400 56
6. View Main. using partial information Self-Maintenance - update Ø SPJ view is self-maintainable in respect to updates on nonexposed attributes when the key attributes are 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 11/1/2020 CHAR(20), rating INTEGER PRIMARY KEY (name) ) Distinguished CREATE VIEW Good. Flights(src, dest) AS SELECT F. from, F. to FROM Flight F, RCompany C WHERE F. company = C. name AND C. rating > 5 AND F. price < 400 57
6. View Main. using partial information Self-Maintenance - update Update of duration does not affect the view 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 11/1/2020 CHAR(20), rating INTEGER PRIMARY KEY (name) ) Distinguished CREATE VIEW Good. Flights(src, dest) AS SELECT F. from, F. to FROM Flight F, RCompany C WHERE F. company = C. name AND C. rating > 5 AND F. price < 400 58
Outline 1. Introduction to views 2. The problem of materialized view maintenance 3. The idea behind incremental view maintenance 4. Dimensions – the problem space 5. View maintenance using full information § 6. The counting algorithm View maintenance using partial information § Self-maintenance 7. Applications 8. Open problems 11/1/2020 60
Applications of materialized views Ø Query speed-up Ø Integrity constraint checking Ø Query optimization Ø Data warehousing Ø Chronicle systems (~ continuous queries) Ø Mobile systems Ø Data visualization 11/1/2020 Ø ……… 61
7. Applications Query speed-up Ø 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? 11/1/2020 62
7. Applications Integrity constraint checking Ø Static integrity constraints can be modeled as materialized views that are required to be empty Ø Example: CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL, company CHAR(20) ) 11/1/2020 CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT F. company FROM Flight F GROUP BY F. company HAVING COUNT(*) > 100 ) ) 63
7. Applications Integrity constraint checking Ø Example: CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL, company CHAR(20) ) CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT F. company FROM Flight F GROUP BY F. company HAVING COUNT(*) > 100 ) ) Can be modeled as CREATE VIEW Monopolies AS SELECT F. company FROM Flight F 11/1/2020 GROUP BY F. company HAVING COUNT(*) > 100 CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT * FROM Monopolies ) ) 64
7. Applications Integrity constraint checking Ø Example: CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL, company CHAR(20) ) CREATE VIEW Monopolies AS SELECT F. company FROM Flight F 11/1/2020 GROUP BY F. company HAVING COUNT(*) > 100 What happens on updates to the base relation? ) CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT F. company FROM Flight F GROUP BY F. company HAVING COUNT(*) > 100 ) CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT * FROM Monopolies ) ) 65
7. Applications Integrity constraint checking Ø Example: CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL, company CHAR(20) ) CREATE VIEW Monopolies AS SELECT F. company FROM Flight F 11/1/2020 GROUP BY F. company HAVING COUNT(*) > 100 CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT F. company FROM Flight F GROUP BY F. company HAVING COUNT(*) > 100 ) How can we do better? ) CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT * FROM Monopolies ) ) 66
7. Applications Integrity constraint checking Ø Example: CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL, company CHAR(20) ) CREATE VIEW Monopolies AS SELECT F. company FROM Flight F 11/1/2020 GROUP BY F. company HAVING COUNT(*) > 100 Which additional view shall we materialize? CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT F. company FROM Flight F GROUP BY F. company HAVING COUNT(*) > 100 ) ) CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT * FROM Monopolies ) ) 67
7. Applications Integrity constraint checking CREATE TABLE Flight ( from CHAR(20), to CHAR(20), price REAL, company CHAR(20) ) CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT F. company FROM Flight F GROUP BY F. company HAVING COUNT(*) > 100 ) CREATE VIEW Monopolies (name) AS SELECT F. company FROM Flight F GROUP BY F. company HAVING COUNT(*) > 100 Ø ) CREATE ASSERTION No. Monopoly CHECK ( NOT EXISTS ( SELECT * FROM Monopolies ) ) Materialize an additional view for maintaining Monopolies: CREATE VIEW Helper (name, total) AS SELECT F. company, COUNT(*) FROM 11/1/2020 Flight F GROUP BY F. company CREATE VIEW Monopolies(name) AS SELECT H. company FROM Helper H WHERE H. total > 100 68
7. Applications Query optimization Ø 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 11/1/2020 69
7. Applications Query optimization – the Microsoft SQL Server approach Ø 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 Ø 11/1/2020 Language limitation: Select-Project-Join-Group. By 70
Outline 1. Introduction to views 2. The problem of materialized view maintenance 3. The idea behind incremental view maintenance 4. Dimensions – the problem space 5. View maintenance using full information § 6. The counting algorithm View maintenance using partial information § Self-maintenance 7. Applications 8. Open problems 11/1/2020 71
7. Open problems Expressiveness of View Definition Language Amount of Information. . . Integrity Constraints …. Recursion Difference Outer-Joins Chronicle Algebra Union Subqueries Aggregation Arithmetic Duplicates Other Views Base Relations Materialized View Insertions Deletions 11/1/2020 Conjunctive Queries Updates Sets of each + Instance dimension Type Group Updates Change view definition of Modification 72
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? § ……. . 11/1/2020 73
Outline 1. Introduction to views 2. The problem of materialized view maintenance 3. The idea behind incremental view maintenance 4. Dimensions – the problem space 5. View maintenance using full information § 6. The counting algorithm View maintenance using partial information § Self-maintenance 7. Applications 8. Open problems 11/1/2020 74
References Ø A. Gupta, I. S. Mumick. Maintenance of Materialized Views: Problems, Techniques, and Application. In Bulletin of the Technical Committee on Data engineering 1995 § Ø R. Ramakrishnan, J. Gehrke. Database Management Systems, Mc. Graw-Hill 2000 § Ø Query independent of update K. Ross, D. Srivastava, S. Sudarshan. Materialized View Maintenance and Integrity Constraint Checking: Trading Space for Time. In SIGMOD 96. § Ø The counting algorithm J. A. Blakeley, P. Larson, and F. Tompa. Efficiently Updating Materialized Views. In SIGMOD 1986 § Ø Introduction to views A. Gupta, I. S. Mumick, V. S. Subrahmanian. Maintaining Views Incrementally. In SIGMOD 1995 § Ø Most of this presentation Applications: Integrity constraint checking J Goldstein, P. Larson. Optimizing Queries Using Materialized Views: A practical 11/1/2020 Scalable Solution. In SIGMOD 2001 § Applications: Query optimization – the Microsoft SQL Server approach 75
- Slides: 74