Oracle PL/SQL: Working with Materialized Views in a 24/7 Reporting Environment

This blog post gives an outline for a process that will provide users with the Last Update, Next Update, and a "nice message" during a materialized view refresh.

The purpose of this blog post is to provide your report writers with a way of identifying whether or not the view is currently being refreshed so they can "try again later". Of course for business-critical up-to-the-second reporting you might not want to be using materialized views at all.


Setting Up The Demonstration System
In order to show this in action we need to setup the following items in a database;

create table MAT_VIEW_TEST
(
  ID          number not null,
  SHORTCODE   varchar2(30) not null,
  DESCRIPTION varchar2(255),
  STARTDATE   date not null,
  ENDDATE     date
)
;

alter table MAT_VIEW_TEST
  add constraint MAT_VIEW_TEST_PK primary key (ID);

insert into MAT_VIEW_TEST (ID, SHORTCODE, DESCRIPTION, STARTDATE, ENDDATE)
values (1, 'IT', 'IT Department', to_date('01-05-2010', 'dd-mm-yyyy'), null);
insert into MAT_VIEW_TEST (ID, SHORTCODE, DESCRIPTION, STARTDATE, ENDDATE)
values (2, 'IT', 'Business Systems Department', to_date('01-01-1980', 'dd-mm-yyyy'), to_date('30-04-2010', 'dd-mm-yyyy'));
insert into MAT_VIEW_TEST (ID, SHORTCODE, DESCRIPTION, STARTDATE, ENDDATE)
values (3, 'FIN', 'Finance', to_date('01-01-1980', 'dd-mm-yyyy'), null);
insert into MAT_VIEW_TEST (ID, SHORTCODE, DESCRIPTION, STARTDATE, ENDDATE)
values (4, 'PRC', 'Procurement', to_date('01-01-1980', 'dd-mm-yyyy'), null);
insert into MAT_VIEW_TEST (ID, SHORTCODE, DESCRIPTION, STARTDATE, ENDDATE)
values (5, 'LEG', 'Legal', to_date('01-01-1980', 'dd-mm-yyyy'), null);
commit;

 create materialized view mat_view_test_mv
  refresh complete 
   start with sysdate 
   next trunc(sysdate)+1 + 5/24 as
  select *
    from mat_view_test
 ;

The materialized view we're creating will refresh immediately and will then schedule itself to refresh at 5am each day going forward.

Aims and Expected Results
The whole point of this Knol is to manage the users expectations. If you provide them a report which is refreshed at 5am and they make a change to the data and then run the report you want them to know why they're not going to see their new record so they don't call your helpdesk and start saying the report is "broken".

The result we're expecting to see (from a simple SELECT * ...) would be;

(1) - This is the date the view was last refreshed and the date the view will next be refreshed.
(2) - As all the records are being returned from the main table the message always reads "OK".

We would expect to get this result at any time other than when the view was being refreshed. When the view is actively being refreshed then the result would normally be blank but what we'd like to see would be a single row returned with the message "REFRESHING".

Building Our View of the Materialized View
Now the values we need (the last and next refresh dates) are stored in SYS.ALL_MVIEWS and SYS.DBA_JOBS respectively. It's a fairly simple process to join these objects together and get the result;

select am.LAST_REFRESH_DATE,
       dj.next_date next_refresh_date,
       'OK' as message,
       mvtv.*
  from MAT_VIEW_TEST_MV mvtv
  left outer join sys.dba_jobs dj
    on dj.what =
       'dbms_refresh.refresh(''"' || USER || '"."MAT_VIEW_TEST_MV"'');'
  join sys.all_mviews am
    on am.owner = user
   and am.mview_name = 'MAT_VIEW_TEST_MV'

We can use a default for the message so that we can change it to something more meaningful in the reporting tool.

Now that we've got our main result we now need to handle the special case when MAT_VIEW_TEST_MV contains no records. The easiest way to do this is to use the DUAL system object to return a single row and do a check in MAT_VIEW_TEST_MV for records. For example;

select null, null, 'REFRESHING', mvtv.*
  from sys.dual
  left join MAT_VIEW_TEST_MV mvtv
    on 3 = 1
 where not exists (select 'x' from MAT_VIEW_TEST_MV)

Now you're probably already spotted that the columns returned by both queries are identical which makes unioning them a lot easier giving you the final piece of SQL;

create view mat_view_test_v as
select am.LAST_REFRESH_DATE,
       dj.next_date next_refresh_date,
       'OK' as message,
       mvtv.*
  from MAT_VIEW_TEST_MV mvtv
  left outer join sys.dba_jobs dj
    on dj.what =
       'dbms_refresh.refresh(''"' || USER || '"."MAT_VIEW_TEST_MV"'');'
  join sys.all_mviews am
    on am.owner = user
   and am.mview_name = 'MAT_VIEW_TEST_MV'
union
select null, null, 'REFRESHING', mvtv.*
  from sys.dual
  left join MAT_VIEW_TEST_MV mvtv
    on 3 = 1
 where not exists (select 'x' from MAT_VIEW_TEST_MV)

You now have a view which will return the contents of the materialized view if it has any or a single record you can check for if it doesn't.