Shulou(Shulou.com)05/31 Report--

This article mainly introduces what is a quick refresh in the 12c materialized view, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

Two definitions are defined before the experiment:

Main table: SH.costs, sample table in example

Base table: SH.mv_costs, the physical table that holds the query result set, that is, the place where the "simulated materialized view" actually holds the data.

Incremental base table: a table that records changes in the data of the master table

What is a quick refresh?

A quick refresh is an incremental refresh, and only the insert, update, and delete operations generated by the main table should be used. It is like an incremental backup in RMAN, only for changes after a level 0 incremental backup.

The implementation of a quick refresh requires the creation of a log file on the primary table that records changes to the primary table. In fact, this file is also a base table, which saves the incremental changes of the main table. The incremental base table records changes depending on the primary key or rowid of the primary table. When a quick refresh is triggered, the change data in the incremental base table is updated to the base table in the materialized view, and then empties itself to wait for the change in the main table. From another point of view, the incremental base table adopts the principle of cache, which collects the data changes and loads them into the materialized view base table in batches to reduce the consumption of complete refresh.

Let's look at the process of fast refresh through an experiment.

[oracle@snow ~] $sqlplus / as sysdba

SYS@cdb > conn sh/sh@pdb1

Currently, there is no primary key in the COSTS table, so you can only use ROWID when creating MV logs.

SH@pdb1 > col owner for A20

SH@pdb1 > col constraint_name for A20

SH@pdb1 > select owner,constraint_name,constraint_type from dba_constraints where table_name='COSTS'

OWNER CONSTRAINT_NAME C

SH SYS_C009967 C

SH SYS_C009966 C

SH SYS_C009965 C

SH SYS_C009964 C

SH SYS_C009963 C

SH SYS_C009962 C

SH COSTS_CHANNEL_FK R

SH COSTS_TIME_FK R

SH COSTS_PRODUCT_FK R

SH COSTS_PROMO_FK R

1. Using rowid to create materialized view log

SH@pdb1 > create materialized view log on costs with rowid

Materialized view log created.

Can be used if there is a primary key

SH@pdb1 > create materialized view log on costs with primary key

two。 Create a materialized view that refreshes quickly

SH@pdb1 > create materialized view mvf_costs

2 segment creation immediate

3 refresh with rowid fast on demand

4 as select promo_id,sum (unit_cost) cost from costs group by promo_id

As select promo_id,sum (unit_cost) cost from costs group by promo_id

*

ERROR at line 4:

ORA-32401: materialized view log on "SH". "COSTS" does not have new values

The error indicates that the materialized view log just created does not meet the requirements. Delete it, adjust it and recreate it.

SH@pdb1 > drop materialized view log on costs

Materialized view log dropped.

Recreate the materialized view log and add the Including new values sentence.

Where sequence records the order of DML operations through promo_id and unit_cost, which is executed on the base table when refreshed

SH@pdb1 >

Create materialized view log on costs with rowid

Sequence (promo_id,unit_cost) including new values

Materialized view log created.

Let's see that the materialized view log will contain two objects when it is successfully created, where MLOG$_COSTS is the incremental base table. When there is a data change in the main table, the implied trigger writes it to the table.

SH@pdb1 > col object_name for A30

SH@pdb1 > select object_name,object_type from user_objects where object_name like'% _ COSTS%'

OBJECT_NAME OBJECT_TYPE

-

I_MLOG$_COSTS INDEX

MLOG$_COSTS TABLE

The modified materialized view log meets the requirements, and the materialized view is created successfully again.

SH@pdb1 >

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

Let's take a look at the family members of the materialized view mvf_costs. MVF_COSTS (MATERIALIZED VIEW) is the logical container of data; MVF_COSTS (TABLE) is the base table for storing query results; MLOG$_COSTS is the incremental base table to save the data changes of the main table COSTS; I_SNAP$_MVF_COSTS is the only index of MV, which is automatically created by Oracle to improve refresh performance; I_MLOG$_COSTS is the index of the incremental base table.

SH@pdb1 > col object_name for A30

SH@pdb1 > select object_name,object_type from user_objects where object_name like'% _ COSTS%'

OBJECT_NAME OBJECT_TYPE

-

I_MLOG$_COSTS INDEX

I_SNAP$_MVF_COSTS INDEX

MLOG$_COSTS TABLE

MVF_COSTS TABLE

MVF_COSTS MATERIALIZED VIEW

Let's test the refresh function.

Add 1000 pieces of data to the main table costs

SH@pdb1 > insert into costs select * from costs where rownum

Commit

Commit complete.

Check the incremental base table to see that 1000 new records have been added.

SH@pdb1 > select count (*) from MLOG$_COSTS

COUNT (*)

-

one thousand

Perform a quick refresh manually

SH@pdb1 > exec dbms_mview.refresh ('MVF_COSTS','F')

PL/SQL procedure successfully completed.

After the quick refresh is completed, the data in the incremental base table is no longer useful.

SH@pdb1 > select count (*) from MLOG$_COSTS

COUNT (*)

-

0

The following is the data change of the materialized view before and after refreshing

SH@pdb1 > select * from mvf_costs

PROMO_ID COST

--

999 9173508.88

350 242949.08

351 258268

SH@pdb1 > select * from mvf_costs

PROMO_ID COST

--

999 9313900.88

350 242949.08

351 258268

Thank you for reading this article carefully. I hope the article "what is a quick refresh in 12c materialized view" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.