Shulou(Shulou.com)06/01 Report--

1. The characteristic of complete refresh is that all the query results on the main table are written to the base table with the same name as the materialized view. When refreshing again, empty all data in the base table before loading the newly executed query results.

Create a fully refreshed materialized view using the keyword refresh complete

Create materialized view mvc_costs refresh complete on demand as select promo_id,sum (unit_cost) cost from costs group by promo_id

Execute the full refresh command manually, with the letter C

Exec dbms_mview.refresh ('MVC_COSTS','C')

If you create a materialized view that contains a large amount of data, you can use the keyword build deferred not to load the data and perform a full refresh manually when the database is not busy. Create materialized view mvn_costs tablespace users build deferred refresh complete on demand as select promo_id,sum (unit_cost) cost from costs group by promo_id

two。 Fast refresh can also be understood as incremental refresh. Incremental data is managed by materialized view logs. After the materialized view is created successfully, the query results have been saved in the base table of the same name in the materialized view. When the master table generates a DML operation, the view log saves it to the incremental base table, synchronizes this part of the data to the materialized view base table when performing a quick refresh, and clears its own data.

You need to create a log to quickly refresh materialized views

Create materialized view log on costs with rowid, sequence (promo_id,unit_cost) including new values

Create a quickly refreshed materialized view using the keyword refresh fast

Create materialized view mvf_costs segment creation immediate refresh fast with rowid on demand as select promo_id,sum (unit_cost) cost from costs group by promo_id

Execute the full refresh command manually, with the letter F exec dbms_mview.refresh ('MVF_COSTS','F')

3. Refresh on submission is a trigger condition, which can be triggered manually or automatically. Manual triggering is the execution of the dbms_mview.refresh package to trigger the refresh event. It is not necessary to execute this command artificially in the application, but to send this command to the database to complete the refresh when certain conditions are met in the program. Another trigger is automatic refresh. The refresh event is triggered when the DML operation of the master table is submitted, thus achieving the effect of real-time synchronization between the master table and the materialized view. This approach is somewhat like logical replication of stream, Oracle GodenGate, or DataGuard.

Submission refresh is cool to use, but there are also points to pay attention to. When the main table is submitted, the base table is also submitted, which increases the system overhead. Part of the performance will be affected in the busy OLTP system. If there is an error in the materialized view update, the main table cannot commit the transaction. two。 The main table and the materialized view must be in the same database. Unable to execute distributed transaction 4. 4 on base table. Materialized views with object types or Oracle supplementary support types are not supported

Let's take a look at create materialized view log on costs with rowid, the creation method of refreshing materialized views on submission.

Create materialized view mvfc_costs refresh on commit as select promo_id,sum (unit_cost) cost from costs group by promo_id

View the data select * from mvfc_costs in the current materialized view

PROMO_ID COST-- 999 9313900.88 242949.08 351 258268

Add 2000 pieces of data insert into costs select * from costs where rownum commit; Commit complete like the main table.

Check again that the materialized view data has changed. SH@pdb1 > select * from mvfc_costs was refreshed successfully.

PROMO_ID COST-- 999 9607463.83 242949.08 351 258268

4. Never refresh what looks like a useless thing, but there is a reason for existence. This is exactly what we use when we need to save a snapshot. Of course, it can also be realized through create table as select. The flexibility of materialized views is that snapshots can be taken, and snapshots can be turned into refreshable materialized views. Take a look at the example: create materialized view mvs_costs never refresh as select promo_id,sum (unit_cost) cost from costs group by promo_id

View view description information select mview_name,refresh_method,refresh_mode,build_mode,fast_refreshable from user_mviews where mview_name='MVS_COSTS'

MVIEW_NAME REFRESH_ REFRES BUILD_MOD FAST_REFRESHABLE-MVS_COSTS NEVER NEVER IMMEDIATE DIRLOAD_LIMITEDDML

You can change a materialized view that is never refreshed to a full refresh using the following sql statement

SH@pdb1 > alter materialized view mvs_costs refresh on demand complete

Materialized view altered.

Status changed from never to complete SH@pdb1 > select mview_name,refresh_method,refresh_mode,build_mode,fast_refreshable from user_mviews where mview_name='MVS_COSTS'

MVIEW_NAME REFRESH_ REFRES BUILD_MOD FAST_REFRESHABLE-MVS_COSTS COMPLETE DEMAND IMMEDIATE DIRLOAD_LIMITEDDML

Horizontal comparison of a variety of materialized view refresh methods will find that some advanced, some backward. In fact, this is not the case, each function has its own usage scenario. The most suitable is the best.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.