10 Replies Latest reply: Jun 24, 2013 8:52 AM by Stefan Jager RSS

    Materialized View Complete, Non-Atomic Refresh?

    B Hall
      I am trying to speed up some over-night batch materialized view updates. They are currently all complete on demand, atomic - so I suggested that we change DBMS_MVIEW.refresh to include atomic_refresh => false since nothing needs to reference them during the refresh (and also save a ton of undo space).

      Is there something I should be aware of with MV's with spatial data with spatial indexes and non-atomic refreshes? I ask because none of them (that I observed) did a non-atomic refresh. This is with 11.2.0.3.5 on AIX.

      Thanks in advance,

      PS, some of these MV's do have parent MV's (all complete refresh) - but some only refresh from base tables. Same results. Partitioning is not being used. Thoughts?

      Bryan
        • 1. Re: Materialized View Complete, Non-Atomic Refresh?
          B Hall
          It appears, at least with the test I just ran on one MV, that it will do a non-atomic refresh - but only if the spatial index is first dropped.

          Why?

          Is this documented behavior someplace that I have not found - or is it a bug?

          Bryan
          • 2. Re: Materialized View Complete, Non-Atomic Refresh?
            Stefan Jager
            Not something that I have ever ran into, but maybe this has something to do with spatial indexes keeping themselves tuned these days?

            Just a shot in the dark ...

            Stefan
            • 3. Re: Materialized View Complete, Non-Atomic Refresh?
              B Hall
              Stefan,

              At 11.2.0.3.5 - one would hope that Oracle code would actually recognize a spatial index, and know what to do with it instead of treating it like a user-defined domain index.

              Anyhow, I have opened SR 3-734370920 for this question. Unless this is some platform specific bug to AIX, I can't believe that no one else would have not noticed and reported it - but a search in MOS turns up nothing.

              Updating the MV's in the non-atomic method is SO much faster. I guessed that it would be at least 2x faster. But in production, a sample MV refresh went from about 1 hour 20 minutes, down to about 2 minutes, including re-creating the spatial index. So I have created a package to automate the drop index, refresh, create index - but it is a pain to have to do this for this "special" index type.

              Bryan
              • 4. Re: Materialized View Complete, Non-Atomic Refresh?
                B Hall
                BTW - Found this post in another topic in this forum - same issue, with 10.2.0.4:

                Refreshing Materialized view create a lot of logs file..
                • 5. Re: Materialized View Complete, Non-Atomic Refresh?
                  Stefan Jager

                  Very true. For one, I wish that they would find a solution to those MDRT-tables. I can understand that they have to be there, but couldn't they be hidden by default or something? It's cluttering a schema.

                   

                  Oracle started with the whole spatial storage in the late nineties - you'd think that by now geometry data would have become mainstream, including the indexing of it.On the other hand, I still think Oracle's solution is the most elegant that exists out there - and I've been around this business since the early nineties, when using a CAD to maintain your drawings was considered flashy

                  • 6. Re: Materialized View Complete, Non-Atomic Refresh?
                    B Hall

                    According to Oracle support - spatial indexes, at least in terms of materialized views, are not recognized as a known Oracle index type. Therefore they are handled just like any user-defined domain index. In this case, if the non-atomic refresh recognizes that there is a domain index on the MV, it will instead refresh it atomic - no matter what you specify.

                     

                    In other words, Oracle won't expend any development dollars to make this work correctly. Instead they plan to update the documentation to note this behavior. Not exactly what we like to hear when we pay support on this add-on each year. I was half-expecting the dreaded "fixed in 12c" answer, but this is worse.

                     

                    Although this issue can be worked around by the user, it is most troublesome when you have parent materialized views that you would normally refresh with the child using DBMS_MVIEW.REFRESH_DEPENDENT. Instead you (the user) will need to write your own code to walk the dependency tree and refresh all the MV's (first dropping the spatial index, refreshing, then re-creating the index), in order. Nor horribly difficult - but again something we as end-users should not need to do. No other Oracle index types are treated this way.

                     

                    Come on Oracle - get on the stick and make this right.

                     

                    Bryan

                    • 7. Re: Materialized View Complete, Non-Atomic Refresh?
                      B Hall

                      Stefan,

                       

                       

                      I can't agree more. I understand why they are there - but I never understand why they show up like any other table. I guess that keeps the code base simpler, since they can be treated like any other generic table.

                       

                       

                      And yes, I still think their solution is the best out there. However, other competing products are not static and are making steady progress. I'm not sure in a few years time I will be able to say the same thing if development on basic functionality is ignored.

                       

                       

                      Bryan

                      • 8. Re: Materialized View Complete, Non-Atomic Refresh?
                        B Hall

                        To Oracle's credit, they are looking at it.

                         

                        BTW, I uploaded example scripts to the SR yesterday that clearly show the buggy behavior if anyone is interested. I'd post them here - but it seems the new editor does not like long lists of SQL...

                         

                        Bryan

                        • 9. Re: Materialized View Complete, Non-Atomic Refresh?
                          B Hall

                          We have a bug!

                           

                          Bug 16934526 - ENH REQ:ALLOW MVIEW COMPLETE ATOMIC_REFRESH => FALSE WITH SPARTIAL INDEX

                          • 10. Re: Materialized View Complete, Non-Atomic Refresh?
                            Stefan Jager

                            Good! Do they specifically mention Spatial Indexes? Then we finally may see some proper integration of spatial data with alfanumerical data :-)

                             

                            Let's hope this will be fixed with the next patch, because even though I haven't run into this particular one I do have customers that use MV's extensively, so it's only a matter of time before one of them will suddenly have issues too.