Oracle and PL/SQL Recipes: A Problem-Solution Approach

11-4. Refreshing a Materialized View on a Timed Interval

Problem

You have a materialized view that must be refreshed on a scheduled basis to reflect changes made to the underlying table.

Solution

First, create the materialized view with a CREATE MATERIALIZED VIEW statement. In this example, a materialized view is created consisting of the department and its total salary.:

CREATE MATERIALIZED VIEW dept_salaries BUILD IMMEDIATE AS SELECT department_id, SUM(salary) total_salary FROM employees GROUP BY department_id;

Display the contents of the materialized view:

SELECT * FROM dept_salaries ORDER BY department_id; DEPARTMENT_ID TOTAL_SALARY ------------- ------------            10         6500            20        20200            30        43500 ...