Oracle Materialized view

Materialized View

Materialized view is a database object which stores the query as well as the results in it.
Normal view will not store the results. Materialized view physically stored in the Database.

Using Materialized view we can
1. Pre-calculate Complex joins
2. Execute the aggregate function

Oracle automatically rewrites the SQL query, so the performance of the execution is faster.

Materialized view is created based on the base table. The table can be in the same database or a remote database.

There are 3 methods available to synchronize with base table
1. Refresh Fast
2. Refresh Complete
3. Refresh Force

There are two modes for Refresh Techniques
1.On Demand (need manual refresh procedure)
2.On Commit (automatic)


1.On Demand (need manual refresh procedure)

Create table learner (learner_id number(10), learner_name varchar2(100), topic varchar2(200), hours number(5) );

create materialized view mv_learner
as
select learner_id , learner_name  from learner;

(or)
create materialized view mv_learner
refresh on demand
as
select learner_id , learner_name  from learner;

Refresh procedure:
sql> execute dbms_mview.refresh('mv_learner ');

when we execute the above procedure materialized view ("mv_learner") will synchronize with the base table ("learner")

2.On Commit (automatic)

create materialized view mv_learner
on commit
as
select learner_id , learner_name  from learner;

Restrictions
Materialized views can only refresh ON COMMIT

1. The materialized view cannot contain object types or Oracle-supplied types.
2. The base tables will never have any distributed transactions applied to them.

Materialized view allows to copy the object from Remote Data base, so we can do the replication using the materialized view.