Partition Change Tracking-based Refresh Without Materialized View Log
I see that this feature made it into 10gR2 ... it bothered me for quite a while that in order to get partition change tracking you had to have a materialized view log, and a couple of years ago Oracle Support couldn't explain to me why it was required.
Well, it's required no longer. Hopefully someone will now be able to tell me how changes to partitions are actually tracked -- it used to be a mystery, apparantly.
posted by David Aldridge @ 8:56 AM
2 Comments:
Good link - Also there is Query Rewrite Using Multiple Materialized Views that one use to be a pain - you had to snowflake the reference data to get that to work in 9.2
Yeah, that's an interesting one. A little esoteric, I think. I've been trying to come up with a potential use for this technique .. so far I thought of a couple of things.
Firstly you could isolate summary data into multiple tables, so that you have different MV's for different months. This might be more robust than having a single MV on an entire fact table, for which the cost of a rebuild (should something in the MV mysteriously break) would be very high. I've had cases where an MV has suddenly "emptied" itself and tried to do a complete refresh on five years worth of data -- yuk.
Another advantage would be that you could make the latest transactional data available immediately without waiting for a bunch of MV's to refresh, and without impacting performance on queries of historic data.
I expect there are others though.
Post a Comment
<< Home