- A view is a logical representation of datafrom one or more tables or views
- A view contains no data itself.
- Only the query is stored in the data dictionary, i.e., the actual data is not copied anywhere.
Uses of View
- To represent a subset or superset of data.
- To restrict data access from other user. (Only by displaying selective columns)
- To present different views of the same kind data.
- To minimize application and data source changes
CREATE VIEW emp_view AS
SELECT ename as Emp_Name, sal*12 as Annual_Sal
FROM empWHERE deptno= 20;
We can retrieve data from a view from any table.
SELECT * from emp_view;
Restrictions on the view subquery
- The view subquery cannot select the CURRVAL or NEXTVAL pseudocolumns.
- If the view subquery selects the ROWID, ROWNUM, or LEVEL pseudocolumns, those columns must have aliases in the view subquery.
- If the view subquery uses an asterisk (*) to select all columns of a table, and if later we are adding new columns to the table, the view will not contain those columns until we re-create the view.
Read-Only View Example
The following statement creates a read-only view named view_ empof the emptable. Only the employee’s names and salary are visible in this view:
CREATE OR REPLACE VIEW view_emp
AS SELECT ename, salFROM emp
WITH READ ONLY;
- Materialized views are disk based and update dynamically base upon the query definition.
- Materialized views are most often used in data warehousing / business intelligence applications where querying large fact tables with thousands of millions of rows would result in query
- Materialized views are primarily used to increase application performance
Read-Only Materialized Views Example