VIEWS in ORACLE

What are different Types of Views in ORACLE? What is the difference between Simple View and Complex View?

• View is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables. A view contains no data itself.
•  A view is simply any SELECT query that has been given a name and saved in the database.  For this reason, a view is sometimes called a named query or a stored query.

Benefits of using Views

• Commonality of code being used. Since a view is based on one common set of SQL, this means that when it is called it’s less likely to require parsing.
• Security. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to.

The syntax for the CREATE VIEW Statement in Oracle/PLSQL is:

CREATE VIEW View_Name AS

  SELECT columns

  FROM tables

  [WHERE conditions];

Feature	Simple Views	Complex Views
Number of tables	One	One or more
Contain functions	No	Yes
Contain groups of data	No	Yes
DML operations through a view	Yes	Not always

Simple Views

·       A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.

·       All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.

·       A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.

·       In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).

·       Using views encourages the use of Shared SQL with the benefit of reduced memory usage.

Create View Employee_Det As

Select E.Empno, 

         E.ename,E.sal,E.deptno,D.dname,D.loc

        From Employee E, Dept D

        Where E.deptno=D.deptno;

Materialized Views

·       Materialized views are schema objects that can be used to summarize, Precompute, replicate, and distribute data. E.g. to construct a data warehouse.

·       A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.

·       The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.

·       You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.

·       Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

·       A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.

 Basic Syntax

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

[ON PREBUILT TABLE]

AS

SELECT <Query>;

The BUILD clause options are shown below.

• IMMEDIATE : The materialized view is populated immediately.
• DEFERRED : The materialized view is populated on the first requested refresh.

The following refresh types are available.

• FAST : A fast refresh is attempted(M View Log File). If materialized view logs are not present against the source tables in advance, the creation fails.
• COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
• FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.

A refresh can be triggered in one of two ways.

• ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
• ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations. An example of the query rewrite functionality is shown below.

The ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.

A materialized view can be manually refreshed using the DBMS_MVIEW package.

EXEC DBMS_MVIEW.refresh('EMP_MV');

Let’s Understand this Practically

Create a Normal and Materialized View with the below Created Table – 

SQL> SELECT * FROM VTAB;

        ID NAME           DEPTNO
---------- ---------- ----------
       101 RAVI               50
       112 PANKAJ             40
       110 SAJAL              10
       106 TIRATH             40

Normal View and Materialized View

SQL> CREATE VIEW V AS SELECT * FROM VTAB;

View created.

SQL> SELECT * FROM V;

        ID NAME           DEPTNO
---------- ---------- ----------
       101 RAVI               50
       112 PANKAJ             40
       110 SAJAL              10
       106 TIRATH             40

/*Now Create aMaterialized View(Make sure you have Privilege to do the same)*/

SQL> CREATE MATERIALIZED VIEW MV
  2  AS SELECT * FROM VTAB;

Materialized view created.

SQL> SELECT * FROM MV;

        ID NAME           DEPTNO
---------- ---------- ----------
       101 RAVI               50
       112 PANKAJ             40
       110 SAJAL              10
       106 TIRATH             40

Unlike views, however, materialized views also store the results of the query in the database. In the following screen note how the ROWID's for the table and the view are identical, indicating the view returns the exact same data stored in the table. The ROWID’s of the materialized view, on the other hand, differ from those of the table. This indicates the materialized view is returning a physically separate copy of the table data.

SQL> SELECT ROWID FROM VTAB;

ROWID
------------------
AAASi7AAEAAAANbAAB
AAASi7AAEAAAANbAAC
AAASi7AAEAAAANbAAD
AAASi7AAEAAAANcAAA

/*ROWID Will be same in case of NORMAL VIEW*/

SQL> SELECT ROWID FROM V;

ROWID
------------------
AAASi7AAEAAAANbAAB
AAASi7AAEAAAANbAAC
AAASi7AAEAAAANbAAD
AAASi7AAEAAAANcAAA

/*ROWID Will be different in case of MVIEW as it Creates a separate physical Copy*/

SQL> SELECT ROWID FROM MV;

ROWID
------------------
AAAS8uAAEAAAANLAAA
AAAS8uAAEAAAANLAAB
AAAS8uAAEAAAANLAAC
AAAS8uAAEAAAANLAAD

The difference between views and materialized views becomes even more evident than this when table data is updated.

SQL> UPDATE VTAB SET NAME=LOWER(NAME);

4 rows updated.

SQL> SELECT * FROM VTAB;

        ID NAME           DEPTNO
---------- ---------- ----------
       101 ravi               50
       112 pankaj             40
       110 sajal              10
       106 tirath             40

SQL> /*Now Select Records From Normal View*/
SQL> SELECT * FROM V;

        ID NAME           DEPTNO
---------- ---------- ----------
       101 ravi               50
       112 pankaj             40
       110 sajal              10
       106 tirath             40

SQL> /*Now check the data in MView*/
SQL> SELECT * FROM MV;

        ID NAME           DEPTNO
---------- ---------- ----------
       101 RAVI               50
       112 PANKAJ             40
       110 SAJAL              10
       106 TIRATH             40

Note how, after the update, the view data matches the table data but the materialized view data does not. Data in materialized views must be refreshed to keep it synchronized with its base table. Refreshing can either be done manually, as below, or automatically by Oracle in some cases.

Execute Dbms_Mview.Refresh('MV');

SQL> SELECT * FROM MV;

        ID NAME           DEPTNO
---------- ---------- ----------
       101 RAVI               50
       112 PANKAJ             40
       110 SAJAL              10
       106 TIRATH             40

SQL> EXECUTE DBMS_MVIEW.REFRESH('MV');

PL/SQL procedure successfully completed.

SQL> --Now Check the Data in MVIEW
SQL> SELECT * FROM MV;

        ID NAME           DEPTNO
---------- ---------- ----------
       101 ravi               50
       112 pankaj             40
       110 sajal              10
       106 tirath             40

SQL> SELECT * FROM VTAB;

        ID NAME           DEPTNO
---------- ---------- ----------
       101 ravi               50
       112 pankaj             40
       110 sajal              10
       106 tirath             40

Now that the materialized view has been refreshed its data matches that of its base table.

Difference between View and Materialized View in database

• Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.
• When we create view using any table, ROWID of view is same as original table but in case of Materialized view ROWID is different.
• In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
• Performance of View is less than Materialized view.
• In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table.
• In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

View	Materialized View
View will not store any data- It's just a stored Query which will fetch data from its Base Table	M-View will store data in Table segment.
Views are virtual only and run the query definition each time they are accessed.	Materialized views are disk based and are updated periodically based upon the query definition.
View is mainly used for Security purpose.	M-view is used for Performance improvement.
Whenever Base table is dropped the View will become inaccessible.	Whenever Base table is dropped the M-View will still be accessible.
We can perform DML operations directly on View.	We can't perform DML operations.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.