Oracle Materialized View

oracle

https://medium.com/oracledevs/lightning-fast-sql-with-real-time-materialized-views-12-things-developers-will-love-about-oracle-54bcc9eac358
https://it.toolbox.com/question/ora-23538-cannot-explicitly-refresh-a-never-refresh-materialized-view-110410

What is a materialized view?

A materialized view is essentially a view, but unlike a view, a materialized view create the physical table from the underlying view. When a view is used in a SELECT statement, oracle may rewrite the SQL statement to use the underlying tables if that is more efficient. Nonetheless, if a view is used in a SQL statement, the data always comes from the underlying table. Unlike a view, the data in the "materialized view" table is not automatically updated from the underlying tables in real time. A job must be scheduled to update this table.

How to create a materialized view?

DROP MATERIALIZED VIEW schemaName.materializedViewName;
CREATE MATERIALIZED VIEW schemaName.MV_materializedViewName
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS 
SELECT * FROM schemaName.viewName;
CREATE MATERIALIZED VIEW QXPLORE.MV_OP_CM_RPT_HCO 
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH COMPLETE
AS 
  SELECT a.cmop_ub_id,
         c.vendorsid,
         a.hcoid,
         d.measureid,
         TO_CHAR (a.op_enc_dt, 'YYYYMM')
             AS period,
         SUM (cat_e)
             AS numerator,
         SUM (cat_e + qxplore.fngetopcatdval (d.measureid, cat_d1, cat_d))
             AS denominator,
         b.contvalue
             AS contvalue,
         d.measuretype
    FROM cmop_ub            a,
         v_cmop_measurevalue b,
         cmcoremeasures     d,
         cmhco              c
   WHERE     a.cmop_ub_id = b.cmop_ub_id
         AND b.measureid = d.measureid
         AND c.hcoid = a.hcoid
GROUP BY a.hcoid,
         a.cmop_ub_id,
         c.vendorsid,
         b.contvalue,
         TO_CHAR (a.op_enc_dt, 'YYYYMM'),
         d.measureid,
         d.measuretype;

How can we create a materialized view with the NEVER REFRESH option?

CREATE MATERIALIZED VIEW schemaName.materializedViewName
ON PREBUILT TABLE WITH REDUCED PRECISION
NEVER REFRESH
AS SELECT ...

How can we refresh a materialized view from Toad?

BEGIN
    DBMS_SNAPSHOT.REFRESH('SCHEMANAME.MATERIALIZEDVIEWNAME');
END;
exec dbms_mview.refresh('qxplore.MV_OP_CM_RPT_HCO','C');

In the above example, the second parameter (C) abbreviate for Complete. Other applicable value for this second parameter are F (Fast), P (FAST_PCT), ? (FORCE).

How can we remove the 'NEVER REFRESH' option from a materialized view?

alter materialized view view_name refresh on demand;

How can we refresh all materialized views?

DECLARE
    v_number_of_failures NUMBER(12) := 0;
BEGIN
    DBMS_MVIEW.REFRESH_ALL_MVIEWS(v_number_of_failures,'C','', TRUE, FALSE);
END;
/

How to schedule the job to refresh the materialized view?

SELECT   * FROM SUPPORT.V_SEMACTIVITYREPORT;  /*THIS REFRESHES THE VIEW CONTAINING BASE SQL WITH LATEST TABLE DATA*/
EXECUTE DBMS_MVIEW.REFRESH( 'SUPPORT.MV_ACTIVITYSEMREPORT',ATOMIC_REFRESH=>TRUE);/* THIS REFRESHES THE MATERIALIZED VIEW WITH THE LATEST DATA */

Right now we use Jenkins to schedule jobs. I am not sure exactly how this is done. Database jobs can also be scheduled to run by Oracle, but I am not sure how to do that neither.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License