Oracle Views

  • View is a database object, which provides security.
  • Views does not store data.
  • View is also called virtual table or window of table.
  • DML, DESC, SELECT allowed on views.

Views are created from base tables. Based on the base tables, views are of two types:
1. Simple View
2. Complex View

Simple View: Simple views are created from one table.
Syntax:
CREATE OR REPLACE view view_name
AS
SELECT * FROM table_name
WHERE condition;

Examples:

CREATE or REPLACE VIEW V1
as
SELECT * FROM emp WHERE deptno=20;

CREATE or REPLACE VIEW V2
as
SELECT * FROM emp WHERE job=’CLERK’;

Complex View: A view which is created from more than one table is called complex view.
Example:

CREATE OR REPLACE VIEW P1
AS
SELECT ename, sal, dname, loc FROM emp, dept
WHERE emp.deptno=dept.deptno;

Materialized View:

  • Oracle 8i introduced Materialized Views.
  • View does not store data where as Materialized View stores data.
  • Materialized View is used to improve performance of the join or aggregate queries. Materialized View stores result of the query.
  • Syntax:

    CREATE MATERIALIZED VIEW View_name
    AS
    select statement;

    Note: BY default, user don’t have permissions to create Materialized View. Use following command to assign privileges to the user.
    GRANT CREATE ANY Materialized View to user_name;

    Powered by k2schools