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 ...
Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.