www.bullraider.com

View And Materialized View

AddThis Social Bookmark Button
  • 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