Oracle Materialized View

oracle

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;

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