View And Materialized View
-
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
Example:
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
-
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
CREATE MATERIALIZED VIEW scott.emp AS
SELECT * FROM scott.emp@orc1.world;
Updatable Materialized Views
CREATE MATERIALIZED VIEW scott.emp FOR UPDATE AS
SELECT * FROM scott.emp@orc1.world;
Powered by Bullraider.com
