Views and Types of Views in Oracle

Views in pl/sql:-

Scenario:-

Above is EMPLOYEE table and EMP_VIEW. View showing only EMPNO, EMPNAME and SALARY columns and not DEPTNO column from EMPLOYEE table. So, whenever you required to show some column from any table you can create view and provide view name to the user so they cannot see the actual table name and owner of the table.
 

There are two types of views:-
 

1)    View

2)    Materialized view

What is view?
 

“View is a virtual table which does not have its owned data, it derives its data from other table called underline or based tables at  run time”. In other words, “A view is a representation of a SQL statement that is stored in memory so that it can be re-used”.

Views are useful for security and information hiding. Some of the advantages of using views:

• Reduce the complexity of SQL statements
• Share only specific rows in a table with other users
• Hide the NAME and OWNER of the base table

To create a view you need to have CREATE VIEW privileges. The syntax of the view is:-

CREATE VIEW VIEW_NAME AS SELECT_STATEMENT;

Suppose, you have EMPLOYEE table with 4 columns, EMPNO, EMPNAME, SALARY and DEPTNO and you also have DEPT table with DEPTNO and DEPTTYPE column. You are frequently using select query on EMPLOYEE table and selecting only 3 columns, EMPNO, EMPNAME and SALARY from EMPLOYEE table and DEPTTYPE from DEPT table, then better you should create view and fetch data from that view only. Let’s see how we can do this:-

If you want to create a view on EMPLOYEE table and you want to show DEPTTYPE column from DEPT table, you can achieve this by creating a view on the EMPLOYEE and DEPT tables:-

You can create view on multiple tables as well like shown below:-

Now let’s select the view:-

If you query EMP_VIEW, it will show only 3 columns named as EMPNO, EMPNAME and SALARY from. It does not show DEPTNO column.

Whenever you will query EMP_VIEW, every time it will go to the EMPLOYEE table and fetch the latest data present in the table.

If you want to drop the view, you can drop the same by using drop keyword like shown below:-

Materialized view in pl/sql:-

Scenario:-
 

Suppose you have one or two database and you need replicate of one of your table, but this replica should retrieve the result very fast and therefore view is not the best option for that because view takes too much time. So, you would need to create a materialized view on the table, which will be refresh on a particular time so the data of the base table should get reflect in the materialized view. Materialized views are helpful to increase the performance of the retrieval.

When you create view on the table, every time you query on the view it goes to the base table(s) and retrieve the fresh data. But materialized view does not show fresh data. It will show you fresh data once it gets refreshed.

What is materialized view?

A materialized view or snapshot as they were previously known is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table. We can use materialized view to replicate table situated on another database or remote database.

How to create materialized view?

As shown above, you can create materialized view in same database where your base table is present or in other database by help of database link (DB Link).

Let’s have an example, how to create materialized view in same database where base table resides:-

Since in oracle, we have facility to set time by when materialized view should get refreshed so we will be adding that option in create script of the materialized view itself.

Without refresh time option:-

CREATE MATERIALIZED VIEW MVIEW_NAME AS SELECT QUERY;

Materialized view on employee table without refresh time option:-

CREATE MATERIALIZED VIEW EMP_MVIEW AS SELECT EMPNO, EMPNAME, SALARY FROM EMP;

We have created above mview on employee table which we would need to refresh at our own. Oracle itself will not refresh it. It is also called as on demand refresh. We can refresh it like:-

EXECUTE DBMS_MVIEW.REFRESH ('EMP_MVIEW');

We have done complete refresh and it truncate the materialized segment and rebuild it by using related queries. Since it truncate and reload data, it can be time consuming process. For faster refresh or to reduce consumption of time, you can create materialized view LOG on base table. LOG will hold all the information about the DML operations performed on the base table from last refresh and while refreshing it will refresh only the records present in the LOG because LOG holds only the changes made to the base table since last refresh.

Materialized view log will always be on the base table site.

How to create materialized view log?

Syntax:-

CREATE MATERIALIZED VIEW LOG ON TABLE_NAME

WITH PRIMARY KEY/ ROWID

INCLUDING NEW VALUES;

In our case, on employee table we can create LOG, if we don’t have primary key constraint then we need to use ROWID clause:-

CREATE MATERIALIZED VIEW LOG ON EMPLOYEE

WITH ROWID

INCLUDING NEW VALUES;

If we have primary key then we can use that in the log creation script:-

CREATE MATERIALIZED VIEW LOG ON EMPLOYEE

WITH PRIMARY KEY

INCLUDING NEW VALUES;

With refresh time option:-

We can create materialized view with refresh time specified in the mview creation script itself like:-

CREATE MATERIALIZED VIEW MVW_NAME

BUILD IMMEDIATE

REFRESH FAST/FORCE / COMPLETE

ON DEMAND

START WITH (STARTING_DATE) NEXT (NEXT_REFRESH_TIME)

DISABLE / ENABLE QUERY REWRITE

AS (SELECT QUERY);

Example:-

For fast refresh we would need to create materialized view log first and then we should create materialized view. So first let’s create materialized view log on the EMPLOYEE table.

CREATE MATERIALIZED VIEW LOG ON EMPLOYEE

WITH PRIMARY KEY

INCLUDING NEW VALUES;

Now create a materialized view:-

Above mview gets refreshed every time on which it was created suppose while creating it was 11 AM, then next day it will get refresh on same time i.e. 11 AM. If you need to refresh mview on a particular time then you can specify start with clause by converting your time in TO_DATE.

What is the difference between view and materialized view?

Sr. No	VIEW	MATERIALIZED VIEW
1	The results are fetched from the view’s base tables when the view is queried.	The query is executed when the materialized view is refreshed
2	A view occupies no space on a disk	A materialized view occupies space on a disk
3	It shows real-time data from the base tables	A materialized view does not reflect real-time data

How to drop materialized view and materialized view log?

We can drop materialized view and materialized view log like:-

Read Also:-