What is Materialized View?

Materialized View is a database object that contain result of query.Materialized view are usually choice when creating summary
tables on aggregate of data.Materialized view does not contain up-to-the-minute information. When ordinary view is queried the data retrieved include changes made up to the last committed transaction.However,when an materialized view is queried the date retried would be at  a state when view was created or last refreshed.

A materialized View is static view that hold data in it.
Materialized view does not support DML operations on it.
To create materialized view you should have permission in schema.
It is used to maintain historic data
It is used for data analysis and reporting purpose
Materialized are mainly created to over come the performance issue and to store historic data.
It is equivalent to snapshot
Materialized views are used in data warehouse
They are used to increase speed of queries on very large database.

Syntax:

     Create Materialized view <schema-Name>
     Refresh[Fast|Complete|FORCE] [ON DEMAND|COMMIT] 
     Start with date
     next date
     with [primary key|rowid]



I have created a Materialized View after then rename a one column on base table, Materialized View updated or not.

 Materialized view is not updated.

I have created a simple view but there is no base table, what happens.
  
   ERROR at line 1:
   ORA-00942: table or view does not exist

   
I have one table I created view and materialized view on a table  If I delete a table can I see the view and materialized view?
  
   We can not see View but we can see materialized view.
   Ordinary View is not stored in Database but Materialized view Stored in
   Database  



When m_views are created mainly?If base table deleted what happen m_views? 

M_view mainly created for overcome  performance issue and to historic data.
It used in data warehouse for data analysis and Reporting Purpose.
If we delete base table, we can access Materialized View.

After inserting data into materialized view and refresh materialized view then what happens on materialized view?

Any Changes made on base tables will reflect  on Materialized view.


What is the diff between dblink & materialized view?(incase of accessing)?

Recently one of my colleague  had a weird error. He was trying to build a materialized view over some tables in his local database and some tables in his remote database using database links the sql to create the view ran fine and provided the results as expected, but when put inside a materialized view statement complained with ORA-00942 errors. Lets say the two databases in question are local and remote, so the sql to create the materialized view to load immediately and refresh everyday is


CREATE MATERIALIZED VIEW MV_CUSTOMERBALANCE
BUILD IMMEDIATE
REFRESH FORCE START WITH ROUND(SYSDATE) + 23/24
NEXT SYSDATE + 1
AS
SELECT customer.name , account.balance, accounttype.name
FROM customer , account@remotedb account, accounttype@remotedb accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id

Oracle started to complain when creating the above materialized view issuing an error ORA-00942: table or view does not exist, but the SQL without the create materialized view command ran fine giving the expected results.


SELECT customer.name , account.balance, accounttype.name
FROM customer , account@remotedb account, accounttype@remotedb accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/
After some searching around and experimenting I found, in the create materialized view statement the database link name can be used only once, which meant we can only use the “remotedb” name once, we got around this restriction by creating two database links to the remote database as REMOTEACCOUNT and REMOTEACCOUNTTYPE and using them in the creation of the materialized view as shown below.


CREATE MATERIALIZED VIEW MV_CUSTOMERBALANCE
BUILD IMMEDIATE
REFRESH FORCE START WITH ROUND(SYSDATE) + 23/24
NEXT SYSDATE + 1
AS
SELECT customer.name , account.balance, accounttype.name
FROM customer , account@remoteaccount account, accounttype@remoteaccounttype accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/

What are  types of refreshing methods.
  
 Refresh can be done in two ways :

 1.Complete Refresh:

  By default materialized view use complete refresh method,in this method           rowid's are recreated When we are refreshing materialized view.This method     degrade performance of application.

   Create Materialized view mv1
   refresh complete
   AS
   Select deptno,sum(sal) from emp group by deptno

   We can refresh materialized view using below package
   exec dbms_mview.refresh('view_name');


 2.Fast Refresh  :
 
   Fast Refresh means since last refresh i.e since last refresh changes made in        base tables are applied to materialized but its not completely recreated.In this    method rowid's are not created 
 
    Before creating fast refresh,we must create materialized log on base tables
           
     Create materialized view logs on emp

     Create Materialized view mv1
     refresh fast
     AS
     Select deptno,sum(sal) from emp group by deptno