When refreshing the result of a view?

Question

I materialize the result of a view in a table. I want to know if their is a way to know when the result of this view change to refresh it. Materialized view and result cache can do that but I don't want to use both techniques. Is their another way to answer to this question?

In fact we have a generic system who materialize and refresh views every n minutes. The cost for the refresh could be high and I try to know if we can know if underlying data has change or not.

We don't use materialized view because full refresh truncate the table and fast refresh work only on table with primary key.

Result cache can't do the job to because we don't have enough memory for that.

So I want to know if we could have a technique to know if underlying data of the view has change since last refresh

Answer 1

You could have a flag table that looks something like this:

CREATE TABLE FlagTable
  SourceTable varchar(30),
  UpdateDate DATETIME,
  Handled CHAR(1) DEFAULT 'N'

Create a trigger on your base tables, where on INSERT, UPDATE, or DELETE of the table, you insert a record into FlagTable with the name of the base table

When you want to check for a refresh, just select WHERE Handled = 'N', refresh the pseudo-view, then set Handled = 'Y'

Answer 2

This is only an heuristic, but as part of your view refresh procedure, you could query and store the max ora_rowscn and row count from your source table(s). Then periodically check those values to see if some of these tables have changed.

select MAX(ora_rowscn), COUNT(*) 
  into last_change_scn, last_change_length 
  from src_table;

If I'm not too wrong, is there was any insert or update in your table, MAX(ora_rowscn) would change. And if there was some delete, COUNT(*) would change.

On the dark side, querying MAX(ora_rowscn) will trigger full table scan. And of course, this can't tell you if those changes will or will not impact the view.

Answer 3

You can do all this in an easier way, by setting the mat_view with refresh on commit option and if possible, also to make it refresh fast on commit. This will guarantee you that each time a change occurs the view will refresh only the new lines (compared to complete refresh, where the query behind the view is executed as a whole).

Now this last method can cause a lot of problems, because the refresh fast on commit option has limitations and if you can't comply with it, you just can't use it. If you can however set this to refresh complete on commit, this can either slow the system as you suggested happens from time to time and also suggests that you can't isolate the source of change.

If you want to see where the most frequent change occurs, I suggest you to use a custom table (per day) where to store the daily result of the query behind the mat_view. Then after a few days, just do something like:

select * from daily_table_day_one
minus
select * from mat_view
union all
select * from mat_view
minus
select * from daily_table_day_one

as the method with ORA_ROWSCN not always works. The method with the triggers works in limited cases and if it isn't required to create those triggers on like 100 tables ... that's ludicrous.

When you find the source of change - for example several columns, coming from some tables, then you might consider rewriting the mat_view query, following some DW principles and create a star schema of the query. This will segment the query and will most certainly speed-up the query. Check where the load is coming from - if those expensive joins, scans whatever can't be avoided, maybe breaking down the query on smaller queries can do the trick if you can materialize them as well, maybe you can set them with refresh fast on commit as well.

It all depends on the implementation. If you can give here an example of the query or a part of it,with the explain plan we can also give you concrete solutions to it.

cheers