Take Advantage of Materialized View

Materialized view is one of the most useful features for data processing in Oracle. We would suggest that materialized views be used when possible.

To create a materialized view, we use "create materialized view as select.. ", just as when we create a view or table. For example, the following SQL script creates a materialized view that calculates the following bank card summary information for each retailer terminal: the number of transactions, amount of transactions and number of unique cards.

create materialized view mv_retailer_terminal_sts
as
select
terminal_id ,
retailer_id ,
terminal_city,
terminal_state ,
terminal_country,
count(1) number_of_txn ,
sum(tran_amt) amount_of_transaction,
count(distinct card_key) number_of_cards
from credit_card_txn_detail a
group by
terminal_id ,
retailer_id ,
terminal_city,
terminal_state ,
terminal_country;

The materialized view captures a snapshot of the "from " tables. It can be refreshed later manually on demand or based on a defined schedule.

For example, we can refresh a materialized view manually using the following command.
execute DBMS_MVIEW.REFRESH('mv_retailer_terminal_sts','C');
('C') means complete refresh.

One of the advantages of using materialized view is that the SQL query used to produce it can be retrieved using the following query:
select query from user_mviews where mview_name=upper('mv_retailer_terminal_sts');

The difference between a view and a materialized view is that a materialized view creates a physical table. Thus a materialized view is faster to query than a view. Thus we suggest that materialized views be used when possible in replacement of tables.