Materialized Views in Oracle Database

[Aandaleeb]

Sponsored Links

Can somebody please give me a some tutorial guidance regarding materialized views/snapshots (preferably in Oracle) and perhaps some sample scripts too.
What are the uses of view object in oracle and how I will create it. Tell me something about this.How the materialized view is different from normal Views.

[Praetor]

The Materialized view is an object of oracle database. The Materialized views, which contains data from the remote tables that are also called as snapshots.
It is used in the term of data warehousing.

Generally we use the materialized view because the base can not be updated by unauthorized user who don't have permission to access the base table.They can only read the data of the view.They can't do any update and all.

But in the case of materialized views we take it as a physical representation of table data and authorized user make some queries and if required they can update the data. It is used to keep your data with a secure manner.

[Katty]

I am going to create a materialized view which is a physical representation of remote tables:

The following syntax will create a primary key materialized view on the emp table stored on the remote database.

SQL>CREATE MATERIALIZED VIEW comp_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_database;
Where "comp_emp_pk" is the name of primary key column and "Remote_database" is the name of the remote database to which you are accessing currently.The Fast is used to create a view log on the master table.you can do the same task to make the view log on master table in this way also :

SQL>CREATE MATERIALIZED VIEW LOG ON emp;

[kelfro]

View :
View is another object of oracle database.It is a set a sql statements which joins one or more tables and retrieve the data from base table according to the structure of base table and view creation. It doesn't contain the data but it kept a structure of base table and retrieve the data whenever user requires.

Materialized View:
In the case of Materialized view ,This is a concept usually applied in Data warehousing, these views contain the data from which it is created. It provides a way to access the data easier and faster from the remote database.The main reason to the creation of Materialized view is to perform calculations and display data from more than one tables using joins concept.