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.
