The document outlines the objectives and functionalities of views in Oracle 12c SQL, including how to create, update, and manage both simple and complex views. It emphasizes the constraints and limitations associated with DML operations on views, particularly regarding key-preserved tables and expressions. Additionally, it introduces inline views, 'TOP-N' analysis, and materialized views for data replication and storage.
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
0 ratings0% found this document useful (0 votes)
12 views30 pages
Chapter 13
The document outlines the objectives and functionalities of views in Oracle 12c SQL, including how to create, update, and manage both simple and complex views. It emphasizes the constraints and limitations associated with DML operations on views, particularly regarding key-preserved tables and expressions. Additionally, it introduces inline views, 'TOP-N' analysis, and materialized views for data replication and storage.
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
• 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
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
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
DML Operations on a Complex View Containing Data from Multiple Tables • DML operations cannot be performed on non- key-preserved tables, but they are permitted on key-preserved tables
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
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
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
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
• 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
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 12c 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