I did not understand your question. What is mandator? Is it a one who gives mandate?
But I am guessing you need a "FAST REFRESHED" materialized view which refreshes the changes only. But I do not think you can just refresh the partition.
Sorry for being vague. All tables and materialized views contain a column mandator. The column mandator is also the partitioning key. A mandator can only read and write its own data; every country has its own mandator (GB, US, ME, etc).
Some mandators contain much more data compared to smaller mandators. Therefore it would be beneficial to refresh dedicated mandators so that mandators with smaller data will be faster refreshed (even for fast refreshed materialized views).
create table t(
1 person found this helpful
But how " refreshing partition" can be more faster than "Fast Refresh" ? If there is change in 2 records in 1 partition,you do not need to refresh the whole partition. fast refresh just refreshes the changes only.
i do not think there is any option in materialized view to just to refresh the partition but you can write your own trigger or procedure for this. But again this would be slower then Fast Refresh.
Yes, I agree with the fast refresh.
There are some more complex materialized views that contain e.g. outer joins. Fast refresh is not possible with outer joins and therefore some materialized views need a complete refresh. For mandators with smaller data the complete refresh finished in a few minutes. "Bigger" mandators need 1h.
AFAIk, PCT is the only method of fast refreshed that can be used. You can write your own trigger or procedure to refresh data for those partitions
We have several partitioned materlialized views. Partitioning key is mandator. We would like to refresh partition independently, e.g. refresh all materialized view partitions for mandator GB (e.g. refresh mandator GB only even if there are also other partitions belonging to other mndators that are stale).
Is it possible to refresh partitions only? I'm aware of partition change tracking but I don't think/know that it is possible to refresh a specific partition.
No - Oracle's refresh process can not be used to refresh a specific partition.
But there is a workaround if you handle the refreshes yourself.
WARNING - this is NOT for the faint of heart. I don't recommend using the approach unless it is absolutely necessary. Make sure you FULLY understand all of the issues involved before using this technique in production.
1. create a pre-built partitioned table to base the MV on
2. create an MV using the pre-built table
3. create an ordinary temp/work table with the same structure as the MV
4. populate the temp/work table with the new (refreshed) partition data
5. execute an EXCHANGE PARTITION of the temp/work table with the 'specific' partition you want to refresh
Arup Nanda has a writeup with a very simple example of how the process basically works. You will need to modify it for your use case.