Materialized View of oracle Advanced Replication

I. Introduction
Materialized View, as its name implies, is a physical view that is actually stored on the hard disk. query performance is better than normal views.

Ii. materialized view principle:
1. generate data
Two major options: build immediate build deferred
Build immediate: generate data based on the master table while creating the Materialized View
Bulid deferred: when creating a materialized view, no data is generated in the materialized view. If no data is generated at this time, you can use EXEC DBMS_MVIEW.Refresh ('mv _ name ', 'C'). Note that full refresh must be used. incremental refresh is used by default. Therefore, the parameter must be C. Because no data is generated before, full data must be used.

2. Refresh
Three refresh Methods: complete fast force
Complete: Completely refresh the entire materialized view, which is equivalent to re-generating the materialized view. At this time, real-time incremental Refresh can also be fully refreshed.
Fast: when data is updated, update the materialized view according to the corresponding rules (in this case, the materialized view log must be created (the materialized view log records the Data Update log)
Force: If incremental refresh is available, incremental refresh is performed. If incremental refresh is unavailable, full refresh is performed. (this option is the default option)
However, from the actual situation, we should try not to use the default option. You can consider using incremental refresh, which is especially effective for large tables. The full update speed of large tables is very slow, especially when indexes exist (in the creation of Materialized View statements, some query conditions may be restricted, leading to the unavailability of incremental refresh, which of the following statements make fast refresh unavailable .....)

Three refresh times: on demand on commit start with/next
On demand: refresh when refresh is required (manual judgment)
On commit: update a base table when there is a submit operation.
Start with: Specifies the first refresh time (generally the current time is specified, but you can also choose not to generate data when creating the materialized view, you can consider refreshing at the specified time, to generate data)
Next: refresh cycle time

Iii. instance demonstration (two host operations, master table host and materialized view host)
1. Create a tablespace (master table machine operation)
Create tablespace TEST_view datafile '/home/oracle/oradata/orcl/TEST_view.dbf'
Size 100 m autoextend on next 50 m;

2. Create a user (master table machine operation)
Create user SC identified by test;
Alter user SC default tablespace TEST_view;

3. Authorize the user (master table machine operation)
Grant connect, resource to SC;
Grant unlimited tablespace to SC;
Grant create session to SC;
Grant create table to SC;
Grant create materialized view to SC;

4. Create a test table (master table machine operation)
Create table SC _test (id int primary key, name char (10 ));

5. Insert data (master table machine operation)
Insert into SC _test (id, name) values (1, 'A ');
Insert into SC _test (id, name) values (2, 'A ');
Insert into SC _test (id, name) values (3, 'A ');
Insert into SC _test (id, name) values (4, 'A ');
Insert into SC _test (id, name) values (5, 'A ');
Insert into SC _test (id, name) values (6, 'A ');
Insert into SC _test (id, name) values (7, 'A ');

6. Create a DBLINK (materialized view machine operation)
Create database link to_test
Connect to "SC" identified by "test"
Using 'SC _ test ';

Delete DBLINK
Drop database link to_test;

7. Create view logs (master table machine operations)
Create materialized view log on SC _test
WITH primary key
Including new values;
Note: Primary keys are required for tables.

8. Create a materialized view (materialized view machine operations)
Create materialized view SC _test_view
REFRESH fast
ON DEMAND
With primary key
Start with sysdate
Next to_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy '), '00: 00: 00'), 'dd-mm-yyyy hh24: mi: ss ')
As select * FROM SC. SC _test @ to_test;
Note: scheduled synchronization is used here, and real-time synchronization is required.

Modify Update Time
Alter materialized view SC _test_view
REFRESH fast
ON DEMAND
With primary key
Start with sysdate
Next to_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy '), '00: 00: 00'), 'dd-mm-yyyy hh24: mi: ss ')

9. query the last refresh time. Both tables can be queried.
Select owner, name, MASTER_OWNER, MASTER, to_char (LAST_REFRESH, 'yyyy-MM-DD HH24: MI: ss') last_update from dba_mview_refresh_times;
Select owner, mview_name, LAST_REFRESH_TYPE, to_char (LAST_REFRESH_DATE, 'yyyy-MM-DD HH24: MI: ss') last_update from user_mviews;

Author "Novelty"