Materialized Views in Oracle

In some scenarios, you won't be in a position to improve the performance of a query without changing the database design. In those scenarios you can think of using materialized view.

Materialized view is a database object that contains the result of a query (it actually contains the rows).


SQL> create materialized view mview
2 build immediate
3 refresh complete on demand
4 as select count(1) from
5 (select state,zip,city,count(1) from mv_test1 group by state,zip,city);

Materialized view created.

Depends on the REFRESH mode specified MV will be refreshed ON DEMAND, ON COMMIT or at specific time.

Advantage:
Main advantage is Improved query perfomance.

Example:
Same query which is used to create the MV is executed here and it took 1.26 secs.

SQL> select count(1) from
2 (select state,zip,city,count(1) from mv_test1 group by state,zip,city);

COUNT(1)
----------
347

Elapsed: 00:00:01.26.

But selecting from MV took 0.01 seconds.

SQL> select * from mview ;

COUNT(1)
----------
347

Elapsed: 00:00:00.01

Disadvantage:

If you use refresh option as ON COMMIT and the MV is created on a transaction table which go through several DML operations, DB resources will be utilized to refresh the MV, and in turn it will slow down the db performance.