Partition Change Tracking Fast Refresh Workaround?
I wrote a while ago that I didn't think that Partition Change Tracking (PCT) fast refresh was worth using, because eventually something will cause the refresh to fail and then, for various silly reasons, you can only get PCT FR back by completely rebuilding your MV in one shot. This is not a practical endeavour in many cases, hence PCT is not worth so much.
However, it has now occured to me that if one kept a backup copy of the data in the PCT-based MV, even if it is only as a regular table, then in the event of disaster such a table could be brought up to dat with respect to the master tables(s) and with the correct MV definition placed over it temporarily the complete rebuild of the PCT-based MV could be rewritten to effectively be a full table scan of this backup. that ought to be more achievable I suppose.
It still seems like a lot of trouble though.
posted by David Aldridge @ 2:04 PM
4 Comments:
But if you created the MV over a prebuilt table you could drop the MV and the data would still be there and then recreate it and then....
just like I had to talk one of my team through at 5:00am today when a partition exchange went pear-shaped... So much for being on holiday :(
Yes indeed ... prebuilt tables are the way to go, but the more I look at them, the more I don't want to touch materialized views at all. 75% of the problems in our system right now are related to MV refresh, and the path to happiness appears to be to upgrade to 10g and use the DBMS_ADVANCED_REWRITE package on ETL-maintained summary tables. Like that'll happen soon.
to tell you the truth we use 9.2 mviews over pre-built tables and maintain them through our own summary ETL code and then lie to Oracle that qurery used to create them is over the base table. Nesting mviews is often more trouble than its worth. If only we had re-write equivalance... but the customer's query tool is not certifed against 10.
Tsh. This whole version certification thing grates my cheese. How long has 10g been out now, and how much real difference is there for query tools? Especially if you're using 9i client, of course.
* sigh *
Post a Comment
<< Home