Materialized views Practical and theory

what is  materialized view
A materialized view is a database object that contains the results of a query.
Materialized views, which store data based on remote tables, are also known as snapshots.
A materialized view is a view where the query has been executed and the results has been stored as a physical table.
Difference between view and materialized view.
Materialized view- But Materialized views are schema objects, it storing the results of a query in a separate schema object take up storage space and contain data. This indicates the materialized view is returning a physically separate copy of the table data.
View- A view is nothing but a SQL query, takes the output of a query and makes it appear like a virtual table, which does not take up any storage space or contain any data.

 Syntax
Sql>create materialized view 
Bulid [immediate|deffered]
Refersh [fast|complete|force]
On [commit|demand]
As
Select * from @;

Build clause options
Immediate : The materialized view is populated immediately
Deferred    : The materialized view is populated on the first requested refresh.

Refersh types                          
Fast        : A fast refresh is attempted. 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 referesh 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.

Privilages
Check the user who will own the materialized views has the correct privileges. At minimum they will require the CREATE MATERIALIZED VIEW privilege. If they are creating materialized views using database links, you may want to grant them CREATE DATABASE LINK privilege also.

Sql>Conn sys@db2
Sql>Grant create materialized view to u1;
Sql>Grant create database link to u1;

Create materialized view
Sql>Connect to the materialized view owner(u1/u1) and create the database link and the materialized view itself.
Sql>Conn u1/u1@db2

syntax for Db link-if u want connect another server Db
sql>Create database link connect to identified by using <’tns-name’>;

Example
Sql>Create database link db1 connect to u1 identified by u1 using ‘db1.sainora’;
Sql>Create materialized view mv
Build immediate
Refresh force
On demand
As
Select * from t1@db1.sainora;

Materialized view logs
The CREATE MATERIALIZED VIEW LOG command is used to create a log of activity on a materialized view’s base table.
 DML statements against this base table are logged and used to apply those changes to the materialized view. Without this log, fast refreshes of the materialized view are not possible and the only option of refreshing the materialized view is a complete refresh.
 A fast refresh is quicker than a complete refresh. 
The trade off is that these materialized view logs consume space. 

The base table’s owner must own the materialized view log. 
Sql>Conn u1/u1@db1
Sql>Create materialized view log on u1.t1 With primary key;

Refresh materialized view
If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary.
Sql>exec dbms_mview.refresh(‘mv’);

Drop log
Sql>drop materialized view log on u1.t1;

Drop materialized view
Sql>drop materialized view mv;