7 Replies Latest reply: Dec 4, 2013 11:53 AM by TimWong765 RSS

    Refresh specific partition of materialized table

    TimWong765


      DB version 11.2.0.3 on Linux

       

      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.

        • 1. Re: Refresh specific partition of materialized table
          TSharma-Oracle

          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.

          • 2. Re: Refresh specific partition of materialized table
            TimWong765

            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(

            mandator number

            ...

            );


            • 3. Re: Refresh specific partition of materialized table
              TSharma-Oracle

              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.

              • 4. Re: Refresh specific partition of materialized table
                TimWong765

                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.

                • 5. Re: Refresh specific partition of materialized table
                  TSharma-Oracle

                  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

                  • 6. Re: Refresh specific partition of materialized table
                    rp0428
                    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.

                     

                    Basically you:

                    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.

                    http://arup.blogspot.com/2010/04/online-materialized-view-complete.html