8 Replies Latest reply: Mar 5, 2013 1:58 AM by 952700 RSS

    Refresh of materialized views

    952700
      Hi there,
      i've got a question to the refreshment of materialized views: when updating or inserting / deleting data of some specific tables, my materialized view should be updated.
      First of all, i though to make that happen by using triggers on the specific table and the DBMS_MVIEW.REFRESH() mechanism. But as i have read pretty often, it isn't recommended doing that with triggers.

      But how can I get what I want :-)

      Thank a lot for your answers,
      Martin

      Edited by: 949697 on Mar 4, 2013 1:11 AM
        • 1. Re: Refresh of materialized views
          mtefft
          You probably want to investigate using the REFRESH FAST ON COMMIT clause in your materialized views.

          http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#sthref224

          Note that 'REFRESH FAST' has a number of restrictions on the complexity of your materialized view.
          • 2. Re: Refresh of materialized views
            moreajays
            Hi Martin,

            when updating or inserting / deleting data of some specific tables, my materialized view should be updated.
            -> Yes it does get updated , if you have created materialized view as "REFRESH ON COMMIT" else you need to do a manual refresh using DBMS_SNAPSHOT.REFRESH or dbms_mview.refresh

            Thanks,
            Ajay More
            http://www.moreajays.com
            • 3. Re: Refresh of materialized views
              952700
              Thanks for your answers,
              the case with FAST REFRESH doesn't work. Our materialized are very complex and are using e.g subselect-statments, analytic functions,UNION, Order, Group by.... According to the documentation, we can't use the FAST REFRESH.

              Using the REFRESH ON COMMIT could be possible. Having a look at the documentation, there is one interesting sentence: A REFRESH ON COMMIT materialized view is refreshed automatically when a transaction that does DML to one of the materialized view's detail tables commits
              What are the materialized view's detail tables? Are that all tables in the FROM-Part of the query?

              Martin
              • 4. Re: Refresh of materialized views
                Pritesh Jani
                Based how often you like to see new data for reporting / querying on MV, you have to decide refresh frequency. You have to keep it in mind that it adds load to your DB if you set it REFRESH ON COMMIT (but that's the way if you like to see the change immediately).

                REFRESH FAST ON COMMIT or REFRESH FAST NEXT <<INTERVAL>>.

                http://oracle.su/docs/11g/timesten.112/e13070/ttsql326.htm

                FAST refresh has limitations

                http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1007028
                • 5. Re: Refresh of materialized views
                  mtefft
                  You can't refresh ON COMMIT if you can't REFRESH FAST.

                  Yes, the 'detail tables' are 'all of the tables used by the materialized view query'.
                  • 6. Re: Refresh of materialized views
                    Pritesh Jani
                    Detail tables are the once on which you are getting aggregates or using analytical functions.
                    • 7. Re: Refresh of materialized views
                      952700
                      As it seems i can't use the REFRESH ON COMMIT functionality on my materialized views, because they are to complex (see my last answer)
                      So, I am at the beginning of the thread again: how can I update my materialized views, when DML-statements are used on some specific tables.
                      Using manually the functions DBMS_SNAPSHOT.REFRESH or dbms_mview.refresh could be possible. But inserting them in a trigger isn't such a good idea, so far as i know.

                      Thanks for helping,
                      Martin

                      Edited by: Martin_L on Mar 4, 2013 5:00 AM

                      Edited by: Martin_L on Mar 4, 2013 5:01 AM
                      • 8. Re: Refresh of materialized views
                        952700
                        Push :-)