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 refresh a materialized view from Toad?

BEGIN
    DBMS_SNAPSHOT.REFRESH('SCHEMANAME.MATERIALIZEDVIEWNAME');
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