ORACLE materialized View

In recent days, I am responsible for the P project environment to provide a view of the W system, due to complex query logic, data volume is large, resulting in each query view time, query slow, inefficient, by the W system personnel complaints. Think, or change to materialized view, with materialized view, waist not acid, leg pain, query fast, complaints less, who use who know, hehe.

I really want to elaborate on the contents of the materialized view, which is too large, so I'm only documenting what I've done in this adjustment. Create the simplest materialized view without a quick refresh, so the materialized view log is not created here.

Create materialized view statements as follows (because of data problems, the query content is changed here):

CREATEMaterializedVIEWmv_tablename BUILD IMMEDIATE--Refresh immediately upon creationREFRESH Force--Fast Refresh If quick Refresh is possible, otherwise full refresh          onDEMAND--Refresh ModeSTART withSysdate--First Refresh Time         NEXTSysdate+1/ A   --Refresh time interval          asSELECT 1Id ' A ' name fromDual

When I do it myself in plsql, sometimes it causes the materialized view to fail because of the reason of the comment, so when creating it, delete the comment.

If you want to modify the refresh rate in some cases, you can use the following command to modify the refresh interval

ALTER VIEW Mv_tablename       REFRESH        Force on  DEMAND        with  sysdate        NEXT Sysdate+1/144;

Query materialized view last refresh time

SELECT last_refresh_date        from      WHERE='mv_tablename';

Manually refresh materialized views

BEGIN        Dbms_mview.refresh ('mv_tablename');     END;

If you delete materialized views, you can use the following statement

 drop  materialized view  mv_tablename;