One of the in-depth studies of Oracle's Materialized View (mview)

One of the in-depth studies of Oracle's Materialized View (mview)

One of the in-depth studies of Oracle's Materialized View (mview)

Go to anysql.net

A materialized view is provided from Oracle 8i to Improve the access speed of applications through pre-computed intermediate tables. It is a useful technology under specific circumstances. in addition, the materialized view can also be used for data replication, and more applications are available on this page. in mview, refreshing is often slow. How can this problem be improved? First, we will study the refresh process. The following is the corner book used to create the demo table:

Create Table t_mvlog (col1 varchar2 (20 ));
Create materialized view log on t_mvlog
With rowid, sequence;
Create materialized view mv_t_mvlog
Refresh fast
With rowid
As
Select rowid r_id, A. * From t_mvlog;

We made a SQL _trace for dbms_mview.refresh. In this example, I inserted a dozen records in the base table and tracked them. The first step is:

Update "anysql". "mlog $ _ t_mvlog" set snaptime $ =: 1
Where snaptime $>
To_date ('1970-01-01: 00: 00: 00', 'yyyy-MM-DD: hh24: MI: ss ')

Step 2: Obtain the rowid of each row modified during this period.

Select distinct m_row $ from
(
Select m_row $
From "anysql". "mlog $ _ t_mvlog" mlog $
Where "snaptime $">: 1 and ("dmltype $ "! = 'I ')
) Log $
Where (m_row $) not in
(
Select rowid from "t_mvlog" "mas_tab $"
Where mas_tab $. rowid = Log $. m_row $
)

Step 3: obtain the value after refreshing

Select current $. "r_id ",
Current $. "col1 ",
Rowidtochar (current $. rowid) m_row $
From
(
Select "A". rowid "r_id", "a". "col1" "col1" from "t_mvlog" ""
) Current $,
(
Select distinct m_row $ from "anysql". "mlog $ _ t_mvlog" mlog $
Where "snaptime $">: 1 and ("dmltype $ "! = 'D ')
) Log $
Where current $. rowid = Log $. m_row $

Step 4: insert the mview

Insert into "anysql". "mv_t_mvlog" ("r_id", "col1", "m_row $ ")
Values (: 1,: 2,: 3)

The last step is to delete the value in mvlog.

Delete from "anysql". "mlog $ _ t_mvlog" where snaptime $ <=: 1

From this process, we can call four methods: firstUse Quick refresh whenever possible to increase the refresh frequencySecond, you canCreate an index on the snaptime $ field of the mlog $ _ t_mvlog table., ThirdSet session-level db_file_multiblock_read_count, sort_area_size, and other parameters for the refresh process., Fourth, select the timeReorganize the mlog $ _ t_mvlog table to reduce the table size.. These methods are for reference only.