Oracle 11g: SQL Chapter 13 Views Objectives Create a view by using CREATE VIEW command or the CREATE OR REPLACE VIEW command Employ the FORCE and NOFORCE options State the purpose of the WITH CHECK OPTION constraint
Explain the effect of the WITH READ ONLY option Update a record in a simple view Re-create a view Oracle 11g: SQL 2 Objectives (continued) Explain the implication of an expression in a view for DML operations
Update a record in a complex view Identify problems associated with adding records to a complex view Identify the key-preserved table underlying a complex view Drop a view Explain inline views and the use of ROWNUM to perform a TOP-N analysis Create a materialized view to replicate data Oracle 11g: SQL
3 Views Permanent objects that store no data Store a query Two purposes Reduce complex query requirements Restrict users access to sensitive data Oracle 11g: SQL
4 Types of Views Oracle 11g: SQL 5 Creating a View You use the CREATE VIEW keywords to create a view Use OR REPLACE if the view already exists
Use FORCE if the underlying table does not exist at the time of creation Provide new column names if necessary Oracle 11g: SQL 6 Creating a View (continued) WITH CHECK OPTION constraint if used, prevents data changes that will make the data
subsequently inaccessible to the view WITH READ ONLY prevents DML operations Oracle 11g: SQL 7 Creating a Simple View Only references one table no group functions, GROUP BY clause, or expressions
Oracle 11g: SQL 8 DML Operations on a Simple View Any DML operations are allowed through simple views unless created with WITH READ ONLY option DML operations that violate constraints on the underlying table are not allowed
Oracle 11g: SQL 9 Creating a Complex View A complex view may contain data from multiple tables or data created with the GROUP BY clause, functions, or expressions Type of DML operations allowed depends on various factors
Oracle 11g: SQL 10 DML Operations on a Complex View with an Arithmetic Expression Oracle 11g: SQL 11
DML Operations on a Complex View Containing Data from Multiple Tables DML operations cannot be performed on nonkey-preserved tables, but they are permitted on key-preserved tables Oracle 11g: SQL 12 DML Operations on a Complex View Containing Data from Multiple Tables
(continued) Oracle 11g: SQL 13 DML Operations on a Complex View Containing Functions or Grouped Data DML operations are not permitted if the view includes a group function or a GROUP BY
clause Oracle 11g: SQL 14 DML Operations on a Complex View Containing Functions or Grouped Data (continued) Oracle 11g: SQL
15 DML Operations on a Complex View Containing DISTINCT or ROWNUM DML operations on a view that contains the DISTINCT keyword or ROWNUM are not permitted Oracle 11g: SQL
16 Dropping a View Use DROP VIEW command Oracle 11g: SQL 17 Creating an Inline View An inline view is a temporary table created by using
a subquery in the FROM clause It can only be referenced while the command is being executed Most common usage TOP-N analysis Oracle 11g: SQL 18 TOP-N Analysis ORDER BY included to identify top values:
Descending for highest values Ascending for lowest values Extract data based on ROWNUM Oracle 11g: SQL 19 TOP-N Analysis (continued)
Oracle 11g: SQL 20 Materialized Views Replicate data Store data retrieved from view query Referred to as snapshots Oracle 11g: SQL
21 Materialized Views (continued) Oracle 11g: SQL 22 Materialized Views (continued) Oracle 11g: SQL
23 Summary A view is a temporary or virtual table that is used to retrieve data that exists in the underlying database tables The view query must be executed each time the view is used A view can be used to simplify queries or to restrict access to sensitive data
A view is created with the CREATE VIEW command A view cannot be modified; to change a view, it must be dropped and then re-created, or the CREATE OR REPLACE VIEW command must be used Oracle 11g: SQL 24 Summary (continued) Any DML operation can be performed on a simple query if it does not violate a constraint
A view that contains expressions or functions, or that joins multiple tables, is considered a complex view A complex view can be used to update only one table; the table must be a key-preserved table Data cannot be added to a view column that contains an expression DML operations are not permitted on non-key-preserved tables Oracle 11g: SQL 25
Summary (continued) DML operations are not permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or
the DISTINCT keyword Oracle 11g assigns a row number to every row in a table to indicate its position in the table; the row number can be referenced by the keyword ROWNUM A view can be dropped with the DROPVIEW command; the data is not affected, because it exists in the original tables An inline view can be used only by the current statement and can include an ORDER BY clause TOP-N analysis uses the row number of sorted data to determine a range of top values Materialized views physically store view query results
Oracle 11g: SQL 26
