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