上海交通大学：《数据库系统原理 The principle of Database System》课程教学资源（课件讲稿）chapter8 Views, Indexes

Chapter 8 Views,Indexes Virtual and Materialized Views Speeding Accesses to Data 1

1 Chapter 8 Views, Indexes Virtual and Materialized Views Speeding Accesses to Data

Views A view is a relation defined in terms of stored tables (called base tables and other views. ▣Two kinds: 1.Virtual not stored in the database; just a query for constructing the relation. 2.Materialized actually constructed and stored. 2

2 Views † A view is a relation defined in terms of stored tables (called base tables ) and other views. † Two kinds: 1. Virtual = not stored in the database; just a query for constructing the relation. 2. Materialized = actually constructed and stored

Declaring Views ▣Declare by: CREATE [MATERIALIZED]VIEW AS ; ▣Default is virtual. 3

3 Declaring Views † Declare by: CREATE [MATERIALIZED] VIEW AS ; † Default is virtual

Example:View Definition ▣CanDrink(drinker,beer）is a view "containing"the drinker-beer pairs such that the drinker frequents at least one bar that serves the beer: CREATE VIEW CanDrink AS SELECT drinker,beer FROM Frequents,Sells WHERE Frequents.bar Sells.bar;

4 Example: View Definition † CanDrink(drinker, beer) is a view “containing” the drinker-beer pairs such that the drinker frequents at least one bar that serves the beer: CREATE VIEW CanDrink AS SELECT drinker, beer FROM Frequents, Sells WHERE Frequents.bar = Sells.bar;

Example:Accessing a View d Query a view as if it were a base table. Also:a limited ability to modify views if it makes sense as a modification of one underlying base table. ▣Example query: SELECT beer FROM CanDrink WHERE drinker ='Sally' 5

5 Example: Accessing a View † Query a view as if it were a base table. „ Also: a limited ability to modify views if it makes sense as a modification of one underlying base table. † Example query: SELECT beer FROM CanDrink WHERE drinker = ’Sally’;

What Happens When a View Is Used? O The DBMS starts by interpreting the query as if the view were a base table. Typical DBMS turns the query into something like relational algebra. OThe queries defining any views used by the query are also replaced by their algebraic equivalents,and spliced into"the expression tree for the query. 6

6 What Happens When a View Is Used? zThe DBMS starts by interpreting the query as if the view were a base table. – Typical DBMS turns the query into something like relational algebra. zThe queries defining any views used by the query are also replaced by their algebraic equivalents, and “spliced into ” the expression tree for the query

Example:View Expansion SELECT beer FROM CanDrink PROJpeer Tbeer WHERE drinker 'Sally'; SELECT drinker=Sally' Odrinker=Sally! CREATE VIEW CanDrink CanDrink AS πdrinker,beer SELECT drinker, PROJdrinker,beer beer FROM Frequents, Sells JOIN WHERE Frequents.bar Frequents Sells Sells.bar; Frequents Sells 7

7 Example: View Expansion PROJbeer SELECTdrinker=‘Sally’ CanDrink PROJdrinker, beer JOIN Frequents Sells SELECT beer FROM CanDrink WHERE drinker = ’Sally’; CREATE VIEW CanDrink AS SELECT drinker, beer FROM Frequents, Sells WHERE Frequents.bar = Sells.bar;

DMBS Optimization It is interesting to observe that the typical DBMS will then optimize" the query by transforming the algebraic expression to one that can be executed faster. Key optimizations: 1.Push selections down the tree. 2.Eliminate unnecessary projections. 8

8 DMBS Optimization z It is interesting to observe that the typical DBMS will then “optimize ” the query by transforming the algebraic expression to one that can be executed faster. z Key optimizations: 1. Push selections down the tree. 2. Eliminate unnecessary projections

Example:Optimization PROJpeer Notice how most tuples JOIN are eliminated from Frequents before the SELECTdrinker=sally Sells expensive join. Frequents 9

9 Example: Optimization PROJbeer JOIN SELECTdrinker=‘Sally’ Sells Frequents Notice how most tuples are eliminated from Frequents before the expensive join

Modifying Views ▣View Removal Drop view canDrink; Updates on more complex views are difficult or impossible to translate,and hence are disallowed. Most SQL implementations allow updates only on simple views (without aggregates) defined on a single relation 10

10 Modifying Views †View Removal Drop view canDrink; † Updates on more complex views are difficult or impossible to translate, and hence are disallowed. † Most SQL implementations allow updates only on simple views (without aggregates) defined on a single relation