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
40 6500
50 297100
60 35000
70 10000
80 305600
90 58000
100 51600
110 20300
7000
Use the EXEC DBMS_REFRESH.MAKE procedure to set up a refresh of the materialized view:
EXEC DBMS_REFRESH.MAKE ('HR_MVs', 'dept_salaries', SYSDATE, 'TRUNC(SYSDATE)+1'),
Change the underlying data of the view.:
UPDATE employees
SET salary = salary * 1.03;
COMMIT;
Note that the materialized view has not changed:
SELECT *
FROM dept_salaries
ORDER BY department_id;
DEPARTMENT_ID TOTAL_SALARY
------------- ------------
10 6500
20 20200
30 43500
40 6500
50 297100
60 35000
70 10000
80 305600
90 58000
100 51600
110 20300
7000
Next, manually refresh the materialized view:
EXEC DBMS_REFRESH.REFRESH ('HR_MVs'),
The materialized view now reflects the updated salaries:
SELECT *
FROM dept_salaries
ORDER BY department_id;
DEPARTMENT_ID TOTAL_SALARY
------------- ------------
10 6695
20 20806
30 44805
40 6695
50 306013
60 36050
70 10300
80 314768
90 59740
100 53148
110 20909
7210
How It Works
The DBMS_REFRESH.MAKE procedure creates a list of materialized views that refresh at a specified time. Although you could schedule a job that calls the DBMS_REFRESH.REFRESH procedure to refresh the view, the MAKE procedure simplifies this automated task. In addition, once your refresh list is created, you can later add more materialized views to the schedule using the DBMS_REFRESH.ADD procedure.
The first argument of the DBMS_REFRESH.MAKE procedure specifies the name of this list; in this example, the list name is HR_MVs. This name must be unique among lists. The next parameter is a list of all materialized views to refresh. The procedure accepts either a comma-separated string of materialized view names or an INDEX BY table, each containing a view name. If the list contains a view not owned by the schema creating the list, then the view name must be qualified with the owner, for example, HR.dept_salaries. The third parameter specifies the first time the refresh will run. In this example, sysdate is used, so the refresh is immediate. The fourth parameter is the interval, which must be a function that returns a date/time for the next run time. This recipe uses 'TRUNC(SYSDATE)+1', which causes the refresh to run at midnight every night.
In this example, the CREATE MATERIALIZED VIEW statement creates a simple materialized view of the total salary by departments, and the data is selected from the view to verify that it is populated with correct data.
Note After adding a 3 percent raise to each employee's salary, we continue to see a materialized view that reflects the old data. The DBMS_REFRESH routine solves that problem.
Although the refresh list was created, the content of the materialized view remains unchanged until the automatic update, which occurs every night at midnight. After the refresh occurs, the materialized view will reflect all changes made to employee salary since the last refresh occurred.
The manual call to DBMS_REFRESH.REFRESH demonstrates how the content of the materialized view changes once the view is refreshed. Without the call to the REFRESH procedure, the content of the materialized view remains unchanged until the next automated run of the REFRESH procedure.