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 <name>AS <query>; ▣Default is virtual. 3
3 Declaring Views Declare by: CREATE [MATERIALIZED] VIEW <name> AS <query>; 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’;