Materialized View refresh

The materialized view is like a real table for front-end database users. It has the same select operations as general tables, but it is actually a view, A view that is regularly refreshed by the system (the specific refresh time is defined when the materialized view is defined). materialized views can be used to implement all functions of the view, materialized views are not used for reading, which greatly improves the reading speed. These views are especially suitable for extracting some information from the big data table and connecting tables with data links. However, materialized views occupy the disk space of the database. The syntax is as follows:
Create materialized view [view_name]
Refresh [Fast | complete | force]
[
On [commit | demand] |
Start with (start_time) Next (next_time)
]
As
{Query statements for creating materialized views}
The specific example is as follows:
Create materialized view an_user_base_file_no_charge
Refresh complete start with sysdate
Next trunc (sysdate + 29) + 5.5/24 -- the red part indicates that the refresh starts from the specified time period (specified by next ).
As
Select distinct user_no
From cw_arrearage t
Where (T. Mon = dbms_tianjin.getlastmonth or
T. Mon = add_months (dbms_tianjin.getlastmonth,-1 ))
Delete Materialized View:
Drop materialized view an_user_base_file_no_charge;
These are common syntaxes used to create a materialized view (materialized view) in Oracle. The meanings of parameters are as follows:
1. Refresh [Fast | complete | force] view refresh method:
Fast: Incremental refresh. assume that the previous refresh time is T1. When you use the fast mode to refresh the materialized view, only the data that has changed from T1 to the current time period is added to the view. to record this change, a materialized view log table is also required to create an incremental refresh materialized view. Create materialized view log on (primary table name ).
Complete: refresh all. It is equivalent to executing a query statement to create a view again.
Force: This is the default data refresh method. When the fast mode is available, data is refreshed in the fast mode; otherwise, the complete mode is used.
2. Mv data refresh time:
On Demand: refresh the data when the user needs to refresh. Here, the user is required to refresh the data manually (or use the job to refresh regularly)
On commit: when data is submitted in the master table, the data in the MV is refreshed immediately;
Start ...... : Refresh Every time (specified by next) from the specified time;
Manually refresh the Materialized View:
Begin
Dbms_mview.refresh (Tab => 'A _ user_base_file_no_charge ',
Method => 'complete ',
Parallelism => 8); -- parallelism Parallel Control Parameter
End;
/
Incremental refresh does not need to use parallelism. Generally, it is unnecessary.
Begin
Dbms_mview.refresh (Tab => 'A _ user_base_file_no_charge ',
Method => 'fast ',
Parallelism => 1 );
End;
/