ORACLE materialized view and oracle Materialized View

ORACLE materialized view and oracle Materialized View

In the last few days, I have provided a View to the W System in the P project environment. Due to the complicated query logic and large data volume, the query speed is slow each time a view is queried, low Efficiency, and complaints from w system personnel. I thought about it. I want to change it to the materialized view. I used the materialized view. The waist is not sour, the legs are not painful, the query is fast, and the complaints are missing. Who knows who to use.

I really want to describe in detail the relevant content of the materialized view, which is too long, so here only records the operations I have done in this adjustment. Create the simplest materialized view without using quick refresh. Therefore, no materialized view logs are created here.

The statement for creating a materialized view is as follows (the query content is changed here due to data issues ):

Create materialized view MV_TableName build immediate -- refresh force immediately upon creation -- If REFRESH is enabled, REFRESH quickly, otherwise, completely refresh on demand -- refresh method start with sysdate -- first refresh time next sysdate + 1/12 -- refresh interval as select 1 id 'A' name FROM dual;

During execution in PLSQL, materialized views may fail to be created due to comments. Therefore, delete comments during creation.

 

To modify the refresh frequency in some cases, run the following command to modify the refresh interval:

ALTER MATERIALIZED VIEW MV_TableName       REFRESH FORCE        ON DEMAND        START WITH SYSDATE        NEXT SYSDATE+1/144;

 

Query the last refresh time of the Materialized View

SELECT last_refresh_date      FROM user_mviews     WHERE mview_name = 'MV_TABLENAME';

 

Manual refresh Materialized View

BEGIN       dbms_mview.refresh('MV_TABLENAME');    END;

 

To delete a materialized view, use the following statement:

DROP MATERIALIZED VIEW MV_TableName;

Oracle Materialized View

No. Your base table is an object table. Although the materialized view has the same functions as the actual table and improves the query speed, there is a fundamental difference. If you want to do this, you can create multiple materialized views. For the same table, or create A trigger on the created Materialized View A, you can generate logs by yourself, write a stored procedure and job for data synchronization. However, we recommend that you use the first one, which is simple.

How Does oracle manually refresh the materialized view?

Exec dbms_mvview.refresh ('mv _ name ');