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

This article introduces the relevant knowledge of "the rapid refresh test of materialized view in mysql and the log analysis of materialized view". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. introduction of materialized view log

1.1 the principle of fast refresh of materialized view: to understand the principle of complete refresh is to delete all the data of materialized view first, and then insert the data of base table into materialized view; but when the data reaches millions of levels, if the source table updates a piece of data, you have to delete all data of materialized view and then insert it.

On the other hand, the quick refresh retains the data of the materialized view, and then the changes of all the data of the base table are recorded in the materialized view log. In this way, if the source table data is still at the level of millions, and a piece of data is updated at this time, according to the log of the materialized view in the process of materialized view refresh, as long as the modified specific record is updated, it can be refreshed quickly.

Simply put, the materialized view log is a database engine automatic Weihua table that is used to track changes to the base table.

1.2 how materialized views are refreshed: we know that if you need to refresh quickly, you need to establish a materialized view log. The Oracle materialized view log can be established as ROWID or PRIMARY KEY type according to the need for rapid refresh of different materialized views. You can also choose whether to include SEQUENCE, INCLUDING NEW VALUES, and a list of specified columns.

Second, the test of fast refresh of materialized view

2.1 create a base table

CREATE TABLE T_JOHN

(

NAME VARCHAR2 (20 BYTE)

SALE VARCHAR2 (20 BYTE)

)

TABLESPACE USERS

RESULT_CACHE (MODE DEFAULT)

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING

2.2 create a materialized view MV_JOHN

CREATE MATERIALIZED VIEW MV_JOHN (NAME,SALE)

TABLESPACE USERS

PCTUSED 0

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOCACHE

LOGGING

NOCOMPRESS

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE USERS

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

REFRESH FAST ON DEMAND

WITH ROWID

AS

SELECT NAME,SALE

FROM T_JOHN

REFRESH FAST ON DEMAND: refresh quickly when you need it

2.3 create a materialized view log

CREATE MATERIALIZED VIEW LOG ON T_JOHN

TABLESPACE USERS

PCTUSED 0

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOCACHE

LOGGING

NOPARALLEL

WITH ROWID

EXCLUDING NEW VALUES

WITH ROWID: refresh the materialized view by ROWID

2.4 after more than 2.4, the data can be modified on the base table.

After running a manual refresh, you can see that the data of the materialized view has also been updated

Begin

Dbms_mView.Refresh ('MV_JOHN')

End

III. Materialized view management

3.1 ORACLE provides views that can be viewed by USER_MVIEW_LOGS, and the refresh of materialized views by users

The name of the materialized view log is MLOG$_ followed by the name of the base table. If the length of the table name is more than 20 digits, only the first 20 digits are taken. When the name is duplicated after truncation, Oracle will automatically add a number to the materialized view log name as the serial number.

3.2 MLOG$_T_WZQ

-- this is a primarykey materialized view log:

SQL > desc MLOG$_T_WZQ

Name Null? Type

GROUP_ID VARCHAR2 (10)

PARENT_GROUP_ID VARCHAR2 (10)

SNAPTIME$$ DATE

DMLTYPE$$ VARCHAR2 (1)

OLD_NEW$$ VARCHAR2 (1)

CHANGE_VECTOR$$ RAW (255)

XID$$ NUMBER

The relevant explanations are as follows:

SNAPTIME$$: is used to indicate the refresh time.

DMLTYPE$$: is used to indicate the type of DML operation, and I represents INSERT,D for DELETE,U and UPDATE.

OLD_NEW$$: is used to indicate whether the value is new or old. N (EW) represents the new value, O (LD) represents the old value, and U represents the UPDATE operation.

CHANGE_VECTOR$$: represents a modification vector, which is used to indicate which field or fields are being modified.

When the refresh is completed, the corresponding logs of MLOG$_T_WZQ will also be cleared, because these logs are no longer necessary to save.

This is the content of "Quick Refresh Test and Log Analysis of materialized views in mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.