Views.pdf - Views INF 551 Wensheng Wu 1 Views \u2022 A view is a \u201cvirtual table,\u201d a relation that is defined in terms of the contents of other tables

Views.pdf - Views INF 551 Wensheng Wu 1 Views u2022 A view...

This preview shows page 1 out of 19 pages.

You've reached the end of your free preview.

Want to read all 19 pages?

Unformatted text preview: Views INF 551 Wensheng Wu 1 Views • A view is a “virtual table,” a relation that is defined in terms of the contents of other tables and views. • Declare by: CREATE VIEW <name> AS <query>; • In contrast, a relation whose value is really stored in the database is called a base table. 2 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: • Recall Frequents(drinker, bar), Sells(bar, beer, price) CREATE VIEW CanDrink AS SELECT distinct drinker, beer FROM Frequents, Sells WHERE Frequents.bar = Sells.bar; 3 Example: Accessing a View • You may query a view as if it were a base table. – There is a limited ability to modify views if the modification makes sense as a modification of the underlying base table. • Example: select * from CanDrink where drinker = 'Bill'; relational algebra in slide 6 is for select beer from CanDrink where drinker = ‘Bill’ 4 What Happens When a View Is Used? • 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. • The queries defining any views used by the query are also replaced by their algebraic equivalents, and “spliced into” the expression tree for the query. 5 Query Execution Plan using Relational Algebra Example: View Expansion Unfolding PROJbeer SELECTdrinker='Bill' This is splicing————> SLIDE 8 But the process of splicing can be removed (CanDrink - cut off) and directly moved to the next part. This is done when db is smart by removing the view. CanDrink PROJdrinker, beer When the view is removed and the select is moved below join for optimization purpose, the select is performed on frequents because drinker comes from Frequents table. In this case, the two projection will be side by side. So, won’t need for two projection. The projection will be only on beer. JOIN Frequents No select here because its a natural join Sells 6 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. reduces the number of tuples you look at 7 Example: Optimization PROJbeer Notice how most tuples are eliminated from Frequents before the expensive join. JOIN SELECTdrinker=‘Bill’ Sells Frequents 8 More Examples: Defining Views Views are relations, except that they are not physically stored. Can be used for presenting different information to different users Employee(ssn, name, department, project, salary) CREATE VIEW Developers AS SELECT name, project FROM Employee WHERE department = 'Development' Payroll has access to all Employees, others only to Developers 9 A Different View Purchase(buyer, seller, product, store, price) Product(name, maker, category) Person(name, city, phone) CREATE VIEW LA-view AS only buyers living in LA SELECT buyer, seller, product, store FROM Person, Purchase WHERE Person.city = ‘LA’ AND Person.name = Purchase.buyer We have a new virtual table: LA-view(buyer, seller, product, store) 10 A Different View We can later use the view: of shoes store here coming from LA-view SELECT name, store name FROM LA-view, Product WHERE LA-view.product = Product.name AND Product.category = 'shoes' 11 What Happens When We Query a View ? Recall: LA-view(buyer, seller, product, store) SELECT name, LA-view.store FROM LA-view, Product WHERE LA-view.product = Product.name AND Product.category = 'shoes' This could have expanded using sub-query too. But that would require the sub-query to be in memory and needs to run it. The join is used for futher optimization. SELECT name, Purchase.store FROM Person, Purchase, Product WHERE Person.city = 'LA' AND Person.name = Purchase.buyer AND Purchase.product = Product.name AND Product.category = 'shoes' 12 Types of Views • Virtual views: Computed only on-demand – slow at runtime – Always up to date • Materialized views – Precomputed offline – fast at runtime – Common in data warehouses – May have stale data 13 Reusing a Materialized View • Suppose I have only the result of LAView: SELECT buyer, seller, product, store FROM Person, Purchase WHERE Person.city = ‘LA’ AND Person.name = Purchase.buyer • and I want to answer the query SELECT buyer, seller FROM Person, Purchase WHERE Person.city = ‘LA’ AND Person.name = Purchase.buyer AND Purchase.product=‘gizmo’. we need to re-write this query because we do not want to recompute the query. Can I answer the query using only the view? select buyer, seller from LAView where product =‘gizmo’; Yes. 14 Query Rewriting Using Views Rewritten query: SELECT buyer, seller FROM LAView WHERE product= ‘gizmo’ Original query: SELECT buyer, seller FROM Person, Purchase WHERE Person.city = ‘LA’ AND Person.name = Purchase.buyer AND Purchase.product=‘gizmo’. 15 Another Example • I still have only the result of LAView: SELECT buyer, seller, product, store FROM Person, Purchase WHERE Person.city = ‘LA’ AND Person.name = Purchase.buyer • but I want to answer the query SELECT buyer, seller FROM Person, Purchase WHERE Person.city = ‘LA’ AND Person.name = Purchase.buyer AND Person.phone LIKE ‘206 543 %’. No, this can’t be done because we do not have person.phone in LAView. 16 Will this additional join reduce the number of rows in purchase? The product in Purchase table is a foreign key with relation to name in Product, So, every Purchase(buyer, seller, product, store) product in purchase table should have a value in Product table. That’s why this additional Product(name, maker, category) inner join will not reduce the number of products in LAView. But if the product was not a Person(name, city, phone) foreign key in Purchase table, then it would have definitely reduced the number of products in LAView. If it was an outer join, all the products in Purchase table would be included regardless of product being foreign key or not. And Now? • I still have only the result of (slightly different) LAView: SELECT buyer, seller, product, store FROM Person, Purchase, Product WHERE Person.city = ‘LA’ AND Person.name = Purchase.buyer AND Purchase.product = Product.name • but I want to answer the query Depends if product in purchase is a foreign SELECT buyer, seller key or not, especially because this is not outer join. It is an inner join. FROM Person, Purchase WHERE Person.city = ‘LA’ AND Person.name = Purchase.buyer 17 And Now? • I still have only the result of: SELECT seller, buyer, Sum(Price) FROM Purchase WHERE Purchase.store = ‘The Bon’ Group By seller, buyer • but I want to answer the query SELECT seller, Sum(Price) FROM Purchase WHERE Purchase.store = ‘The Bon’ Group By seller And what if it’s the other way around? 18 Finally… • I still have only the result of: SELECT seller, buyer, Count(*) FROM Purchase WHERE Purchase.store = ‘The Bon’ Group By seller, buyer • but I want to answer the query SELECT seller, Count(*) FROM Purchase WHERE Purchase.store = ‘The Bon’ Group By seller 19 ...
View Full Document

  • Fall '14
  • Relational model, relation, LAView

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture