1 / 38

Views

Views. Views. A view is a "virtual table" defined using a query You can use a view as if it were a table, even though it doesn't contain data The view is used as a ‘window’ to underlying tables The view is computed every time that it is referenced Changes can be done in both directions:

harding-herman
Download Presentation

Views

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Views

  2. Views • A view is a "virtual table" defined using a query • You can use a view as if it were a table, even though it doesn't contain data • The view is used as a ‘window’ to underlying tables • The view is computed every time that it is referenced • Changes can be done in both directions: View <-> Table

  3. Advantages and Disadvantages • Advantages: • no memory used for views • update of table does not require updating views • gives query processor more choices for optimizing • Disadvantages: • must be recomputed every time used • if tables that view uses are dropped, view data is lost

  4. Example • A View is a query that looks like a table and can be used as a table. CREATE OR REPLACE VIEW Dept20 as SELECT Ename, Job, Sal*12 As AnnualSal FROM Emp Where dept=20;

  5. Example 2: Products table CREATE VIEW [ProductsAboveAveragePrice] AS SELECT ProductName,UnitPrice FROM Products WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

  6. What are views good for?(1) • Simplifying complex queries • Here is another example that allows the user to "pretend" that there is a single table in the database CREATE OR REPLACE VIEW SRB as SELECT S.sid, sname, rating, age, R.bid, day, bname, color FROM Sailors S, Boats B, Reserves R WHERE S.sid = R.sid and R.bid = B.bid

  7. What are views good for?(2) SELECT sname FROM SRB WHERE bid=‘103’ Find names of Sailors who reserved boat 103 using SRB

  8. What are views good for?(3) • Security issues – preventing unauthorized access. • Example: hiding the rating value CREATE VIEW SailorInfo as SELECT sname, sid, age FROM Sailors • grant SELECT on SailorInfo to joe;

  9. Changing Tables through a View

  10. Changing a view changes the underlying tables • We will specify the limitations on the changes we are allowed to perform on a view based on one table only • The guiding principle: We cannot “approach” a value which doesn’t appear in the view • (but many changes do not require “approaching”)

  11. Changing a Table through a View • You can’t insert if the underlying table has non null columns not appearing in the view • You can’t insert or update if any of the view columns referenced in the command contains functions or calculations • You can’t insert, update or delete if the view contains group by or distinct

  12. You can insert a value to the view even if it will not appear in the view after insertion. • You can update a value appearing in the view so that the result will not appear in the view. • This is true unless ‘with check option’ is specified. • CREATE VIEW OldSailors as • SELECT sid, age • FROM Sailors • Where age>50 • With check option • CREATE VIEW OldSailors as • SELECT sid, age • FROM Sailors • Where age>50 • Insert into OldSailors values(1132, 57); • Insert into OldSailors values(1132, 57); Legal! Illegal!

  13. You cannot update a value which doesn’t appear in the view (this requires “approaching”) • You cannot insert or update columns which do not appear in the view • But if you delete a row, it will delete the values of columns which are not in the view as well (does not require “approaching”)

  14. Inserting Allowed CREATE VIEW OldSailors as SELECT * FROM Sailors WHERE age > 50; INSERT INTO OldSailors(sid,sname,age,rating) VALUES(12,‘Joe’,51,10); When we select from OldSailors next time, we will see Joe

  15. Inserting Allowed CREATE VIEW OldSailors as SELECT * FROM Sailors WHERE age > 50; INSERT INTO OldSailors(sid,sname,age,rating) VALUES(12,‘Mary’,49,10); When we select from OldSailors next time, we will not see Mary. But she will appear in Sailors!

  16. Preventing Insertions that are not seen through the View CREATE VIEW OldSailors as SELECT * FROM Sailors WHERE age > 50 WITH CHECK OPTION; INSERT INTO OldSailors(sid,sname,age,rating) VALUES(12,‘Joe’,51,10); OK INSERT INTO OldSailors(sid,sname,age,rating) VALUES(12,‘Mary’,49,10); Error!

  17. Inserting Not Allowed CREATE VIEW SailorsInfo as SELECT sname, rating FROM Sailors WHERE age>50; INSERT INTO SailorsInfo VALUES(‘Joe’,10); Illegal! Why?

  18. Updating Allowed CREATE VIEW SailorsInfo as SELECT sname, rating, age FROM Sailors WHERE age>50; UPDATE SailorsInfo SET rating = 6 WHERE sname = ‘Joe’; Oracle only changes the rating of Joes who are older than 50. Implemented by adding WHERE condition of view to WHERE condition of Update UPDATE Sailors SET rating = 6 WHERE sname = ‘Joe’ and age>50;

  19. Updating Allowed CREATE VIEW SailorsInfo2 as SELECT sname, rating, age FROM Sailors WHERE age>50; UPDATE SailorsInfo2 SET age = age - 1; Will cause tuples to "disappear from the view" Can prevent this with "WITH CHECK OPTION" How is it implemented? UPDATE Sailors SET age = age - 1 WHERE age>50;

  20. Updating Not Allowed if …? CREATE VIEW SailorsInfo2 as SELECT sname, rating, age FROM Sailors WHERE age>50 WITH CHECK OPTION; UPDATE SailorsInfo2 SET age = age - 1;

  21. Updating Not Allowed CREATE VIEW SailorsInfo3 as SELECT sname, rating + age as ra FROM Sailors WHERE age>50; UPDATE SailorsInfo3 SET ra = 7 WHERE sname = ‘Joe’; Illegal! Why? UPDATE SailorsInfo3 SET sname = ‘Joe’ WHERE ra = 7; legal

  22. Deleting Allowed CREATE VIEW SailorsInfo3 as SELECT sname, rating + age as ra FROM Sailors WHERE age>50; DELETE FROM SailorsInfo3 WHERE sname = ‘Joe’ and ra = 56; Oracle only deletes Joes visible through the view. How do you think that this is implement by Oracle? DELETE FROM Sailors WHERE sname = ‘Joe’ and rating + age = 56 and age > 50;

  23. Examples (1) CREATE VIEW OldSailors as SELECT * FROM Sailors WHERE age > 50; UPDATE OldSailors SET rating = 10; Update rating of sailors older than 50

  24. Examples (2) CREATE VIEW OldSailors as SELECT * FROM Sailors WHERE age > 50; UPDATE OldSailors SET age = age +1 WHERE age <= 50; NOTHING!

  25. Examples(3) CREATE VIEW OldSailors as SELECT * FROM Sailors WHERE age > 50; DELETE FROM OldSailors; Remove from Sailors the sailors that are older than 50 DELETE FROM Sailors WHERE age > 50 Implementation

  26. Inserting/Updating/Deleting Not Allowed CREATE VIEW OldSailors as SELECT sname FROM Sailors GROUP BY sname HAVING MIN(age) > 50; DELETE FROM OldSailors; INSERT INTO OldSailors VALUES(‘Joe’);

  27. Inserting/Updating/Deleting Not Allowed CREATE VIEW OldSailors as SELECT distinct sname, age FROM Sailors WHERE age > 50; DELETE FROM OldSailors; INSERT INTO OldSailors VALUES(‘Joe’,55); UPDATE OldSailors SET age = age +1;

  28. Materialized Views • What:A materialized view is a view that actually exists as a table • Why: This can be more efficient than re-computing the view’s query each time it is accessed • How: We specify how often the materialized view is refreshed and how

  29. Null Values "As we know, There are known knowns. There are things we know we know. We also know There are known unknowns. That is to say We know there are some things We do not know.” - Donald Rumsfeld, US Defence Secretary

  30. Null Values in Expressions • The result of an arithmetic expression, over something that is null -> is null (e.g., null*10 = null) • Three-valued logic: true, false, unknown • Nulls in logical expressions: • null AND true -> unknown • null AND false -> false • null OR true -> true • null OR false -> unknown • NOT (null) -> unknown • x { =<, =>, <>, <, >, =} null -> unknown

  31. What will these return? SELECT S.sname FROM Sailors S WHERE S.age = null SELECT S.sname FROM Sailors S WHERE S.age != null

  32. ? ?

  33. Null Values in Expressions (2) • Tuples only pass the WHERE/HAVING condition if the WHERE/HAVING evaluate to true (not false or unknown) • Null verification: • IS NULL (not = NULL) • IS NOT NULL (not <> NULL)

  34. Examples • If x is null • x = 3 -> unknown • null = 3 -> unknown • x = x -> unknown • null = null -> unknown • x * 0 -> = null • null * 0 -> = null

  35. Nulls in Aggregation Functions • count(A): counts non-null As. Returns 0 if all As are null • sum(A), avg(A), min(A), max(A) • ignore null values of A • if A only contains null value, the result is null • count(*): counts ALL rows (even rows that are all null values)

  36. We want the sailors that have not reserved a boat to appear in the result as well SELECT S.sname, R.bid FROM Sailors S, Reserves R WHERE S.sid = R.sid(+) Sailors who have not reserved a boat will have null in the R.bid column

More Related