Oracle Materialized View

Oracle Materialized View

In actual work, multiple data sources are synchronized to a single database for data analysis. These data can not be synchronized in real time. Generally, we extract data from the statistical analysis database through regular tasks for application use.

The general synchronization method can synchronize full and incremental data through the timestamp (the original data may change and the data is inconsistent ), dblink can also be used for Real-time Data Query (which is less efficient than online database performance). Generally, the best way is to create a materialized view and then use schedual job to synchronize scheduled data, the usage of the materialized view is recorded here.

1. Introduction to materialized views

Materialized View is a special physical table. The Materialized view is relative to a common view. A common view is a virtual table, which has many application limitations. Oracle converts any View query to a view SQL statement query. This improves the overall query performance without substantial benefits.

1. materialized view Classification

On demand: REFRESH is performed only when the materialized view is refreshed. That is, the materialized view is updated to ensure data consistency between the base table and the base table;

On commit: Once the base table has a COMMIT, that is, the transaction is committed, it is refreshed immediately, and the materialized view is updated immediately to make the data consistent with the base table;

By default, when a materialized view is created without specifying a type, it is refreshed on demand (on demand)

2. Materialized View

 

Ii. materialized view usage

1. Create a Materialized View

The data in the materialized view comes from the base table, and the refresh start point is recorded in the materialized view log. Therefore, the permission for creating the materialized view must have a base table --> materialized view log (based on the base table) --> Materialized View

Materialized View creation example:

Create materialized view T under dbtest, where the base table is the dept table under scott (1) authorize dbtest users to query scott. deptgrant select on scott. dept to dbtest; (2) Create table T under the dbtest user (if the materialized view is created, add on prebuilt table)
Create table t as select * from scott. dept where 1 = 2;
(3) create materialized view logs under scott user and create materialized view T under dbtest
Create materialized view logs:
Conn scott/tiger;
Create materialized view log on dept;
Grant select on MLOG $ _ DEPT to dbtest;
Create a Materialized View:
Conn dbtest/dbtest;
Create materialized view T
On prebuilt table
Refresh fast on demandasselect deptno, dname, loc, ACOLUMN from scott. dept;

# You can add the build immediate parameter after view T to refresh the materialized view immediately to obtain the data

The REFRESH clause can contain the following parts:
[Refresh [fast | complete | force]
[On demand | commit]
[Start with date] [next date]
[With {primary key | rowid}]

2. Refresh the Materialized View

After the base table is updated (DML), if it is not of the on commit type, the materialized view must be refreshed before the data can be consistent with the base table. The refresh method has a full refresh (COMPLETE), FAST refresh (incremental FAST), FORCE refresh, not refresh (NEVER)

FAST: Incremental FAST refresh

Exec dbms_mview.refresh ('table name', 'F ')

Exec dbms_mview.refresh ('dbtest. t', 'F ');

COMPLETE: full refresh

Exec dbms_mview.refresh ('table name', 'C ');

Exec dbms_mview.refresh ('dbtest. t', 'C ');

FORCE: determines whether a Refresh can be performed quickly. If a Refresh can be performed quickly, perform fast refresh. If not, perform complete refresh.

NEVER: Do not refresh

3. Delete materialized views

drop MATERIALIZED VIEW  mview_name;

4. Delete materialized view logs

The materialized view log is named in mlog $ _ basetablename format.

DROP MATERIALIZED VIEW LOG  on base_table_name;

MLOG $ _ dept deptno primary key column SNAPTIME $ indicates the refresh time DMLTYPE $ indicates the dml operation type, I indicates insert, and d indicates delete, utable update OLD_NEW $ indicates whether the value is a new value or an old value. N (ew) indicates the new value (generally the delete operation), o (ld) indicates the old value (generally the Insert operation), and The u table indicates the update operation. CHANGE_VECTOR $ indicates the modified vector, used to indicate which or which fields are modified XID $ if the with field is followed by the primary key, the materialized view log contains the primary key column. If rowid is followed by with, the materialized view log contains: m_row $: The rowid used to store the changed records. If the object id is followed by with, the materialized view log contains: sys_nc_oid $: the object id used to record each change object. If sequence is followed by with, the materialized view date will include: sequence $: to give each operation a sequence number, so as to ensure that the refresh is performed in order during refresh. If with is followed by one or more column names, the materialized view log contains these columns.

When a dml operation is performed on a basic table, it is recorded in the materialized view log. The specified time is January 1, 4000 00:00:00 (the materialized view is not refreshed ).
If the materialized view log is used by multiple materialized views, the record time refreshed by a materialized view is updated to the refresh time.
Only a materialized view with a quick Refresh can be used for Materialized View logs. If only one materialized view is created, the materialized view logs are cleared after the refresh.

-- When the primary key is used to create a materialized view log, oracle creates a temporary table, RUPD $ _ base table.

5. View materialized views

set line 200;set pagesize 20000;col owner for a15;col mview_name for a30;col query for a60;select owner,mview_name,refresh_method,last_refresh_date,compile_state from dba_mviews;

# If you want to view specific statements, you can view them using the query field.

 

Iii. Appendix

 

Materialized View is a special physical table. The Materialized view is relative to a common view. A common view is a virtual table, which has many application limitations. Oracle converts any View query to a view SQL statement query. This improves the overall query performance without substantial benefits.