Fresh views on refresh views
April 22nd, 2005 by Peter Scott
So, you have got those MV in place and now need to keep them up to date, well there are three options: do-it-yourself, Complete refresh and ‘Fast‘ refresh. To me, fast is not the right name, as under the covers all of the rows affected by data change are updated, this can be quick or mind-blowingly slow.
Complete refresh is simple to understand (just rebuild the table) but in a data warehouse with massive partitioned MVs and only the most recent data changing is perhaps the least useful. Fast (or as I prefer ‘changes‘ ) sounds just the thing. But there is a price to this, well not one price but several: storage for the extra columns needed in the view to hold counts, storage for the extra log tables required to track changes, storage for the index required to locate changes rows quickly, not to mention the overhead of maintaining these structures – undoubtedly, having change logs slows operations on the logged tables.
Perhaps, the biggest drawback with Oracle 9.2 MVs is the way that changes are propagated on partitioned MVs. When partition DDL operations are carried out on an MV (such as create or drop partition) the MV becomes STALE, fast refresh won’t work because of the previous partition operation. So you do a ‘consider fresh’ on the MV and then do a fast refresh, but this then runs row by row by slow and not as PCT partition based change. Working with Oracle’s development team produced an experimental patch (controlled by setting an event in the database) and for which we are grateful that allowed us exploit partition change tracking’s truncate and append approach to refresh. However the other gotcha is the risk of losing data if consider fresh is not carried out immediately after the partition ops and before the data insert into the parent table.
The do-it-yourself option is basically simple, you write the code to maintain the MV’s content. For one of our customers it’s just a simple case of writing code to truncate a partition and append into it, followed by marking the view as fresh.
May 6th, 2005 at 3:44 pm
“Amen” on the partition-based refresh. You know Oracle Support couldn’t even tell us how Oracle knew that a partition-based refresh was required — not a clue. Nor could they tell us why an MV log was required. Tut!
They also refused to consider a problem of an MV not creating as fast refreshable, because they said the methodology we were using was “not supported” — as it happens, it was a straight copy of the methodology in the documentation.
My eyes were rolling like marbles that week.
May 6th, 2005 at 4:50 pm
David
I always had a few problems with support and materialized view refresh – but they did raise a bug for me once :) against the documentation :(
August 30th, 2006 at 9:05 pm
Did this experimental patch ever get anywhere? Are there tricks to get PCT Refresh to work after an “alter mview drop partition …”?
The post talked about writing custom refresh code – that’s understandable, but how do you detect which partitions are now stale? If I have sqlldr loading files (non-direct) and the records do not necessarily go into a predictable partition is there a view or a means to detect which partitions have been updated?
Thanks!
August 31st, 2006 at 6:33 am
The ‘patch’ was to allow 9.2 mimic the default behaviour of 10g – but for 9.2 this approach is risky and requires loads to be done in a certain way – Oracle did not think it would be of general use.
It may be necessary to do an ALTER MATERIAILZED VIEW xxx CONSIDER FRESH after dropping and creating partititions on the materialized view
I don’t know of a view you could use – the technique we us is to load our fact to a stage table (mainly for data validation) and to scan the data for distinct partition keys (dates in our case) we then use this list of partition keys to drive our update