1 2 Previous Next 16 Replies Latest reply: Sep 7, 2010 5:08 AM by user503699 RSS

    materialized view not refreshing

    619521
      Folks,

      Working on Oracle 11g, the problem that I have detailed below appears to show my MV is not refreshing, I am not sure why, maybe I missed a step, any hints much appreciated:-
      SQL> create table t
        2  (
        3    col1 varchar2(10)
        4  );
       
      Table created.
       
      SQL>
      SQL> alter table t add constraint pk_col1 primary key (COL1);
       
      Table altered.
       
      SQL> insert into t select object_name from all_objects where rownum < 6;
       
      5 rows created.
       
      SQL> commit;
       
      Commit complete.
       
      SQL> select * from t;
       
      COL1
      ----------
      ICOL$
      I_USER1
      CON$
      UNDO$
      C_COBJ#
       
      SQL> create materialized view log on t with primary key;
       
      Materialized view log created.
       
      SQL> create materialized view mv
        2  refresh fast on demand
        3  start with sysdate next trunc(sysdate,'mi') + 1/24/12
        4  as
        5  select col1 from t;
       
      Materialized view created.
       
      SQL> select * from mv;
       
      COL1
      ----------
      ICOL$
      I_USER1
      CON$
      UNDO$
      C_COBJ#
       
      SQL> alter table t add col2 varchar2(20);
       
      Table altered.
       
      SQL> update t set col2 = 'hello';
       
      5 rows updated.
       
      SQL> commit
        2  ;
       
      Commit complete.
       
      SQL> select col1,col2 from t;
       
      COL1       COL2
      ---------- --------------------
      ICOL$      hello
      I_USER1    hello
      CON$       hello
      UNDO$      hello
      C_COBJ#    hello
      SQL> select * from mv;
       
      SQL> alter  materialized view mv add col2 varchar2(20);
       
      Materialized view altered.
       
      SQL> select * from mv;
       
      COL1       COL2
      ---------- --------------------
      ICOL$
      I_USER1
      CON$
      UNDO$
      C_COBJ#
       
      SQL> EXEC DBMS_MVIEW.REFRESH('MV');
       
      PL/SQL procedure successfully completed.
       
      SQL> select * from mv;
       
      COL1       COL2
      ---------- --------------------
      ICOL$
      I_USER1
      CON$
      UNDO$
      C_COBJ#
       
        • 1. Re: materialized view not refreshing
          damorgan
          Your materialized view is never going to refresh unless you explicitly tell it to do so.
          refresh fast on demand
          Might be a good time to familiarize yourself with DBMS_MVIEW.REFRESH if this is what you want.
          http://www.morganslibrary.org/reference/dbms_refresh.html

          If not reconsider mixing up syntax for FAST REFRESH and REFRESH ON DEMAND. Two entirely different types of MVs in terms of how most us build them.
          • 2. Re: materialized view not refreshing
            672680
            According to oracle online doc, your MV should be refreshed:

            Quote from doc:
            Specify ON DEMAND to indicate that database will not refresh the materialized view unless the user manually launches a refresh through one of the three DBMS_MVIEW refresh procedures.

            You cannot specify both ON COMMIT and ON DEMAND. If you omit both ON COMMIT and ON DEMAND, then ON DEMAND is the default. You can override this default setting by specifying the START WITH or NEXT clauses, either in the same CREATE MATERIALIZED VIEW statement or a subsequent ALTER MATERIALIZED VIEW statement.

            START WITH and NEXT take precedence over ON DEMAND. Therefore, in most circumstances it is not meaningful to specify ON DEMAND when you have specified START WITH or NEXT.
            • 3. Re: materialized view not refreshing
              672680
              after the DDL to base table, your materialized view will become INVALID.
              After the recompilation, when you query the definition of MV, you find that it is the same with the original one.
              So, this is might be the reason why the new column change is not reflected in MV.
              Also, it seems not the right way to reflect the new column of base table to MV.
              select status from user_objects where object_name='MV';
              --
              INVALID
              
              alter MATERIALIZED VIEW mv compile;
              
              select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MV') FROM DUAL;
              .....
              Edited by: PhoenixBai on Sep 7, 2010 10:53 AM
              • 4. Re: materialized view not refreshing
                619521
                If you look through my example, you will see that I am using 'refresh fast on demand' as well as calling dbms_mview.refresh.
                • 5. Re: materialized view not refreshing
                  619521
                  In my example code, you will notice that when I add the column to the Mv the change is reflected in the structure but the underlying data has not been updated to the Materialized view. I did take your advice and check that the MV did not become invalid, so unfortunately this was not the reason.

                  I have done the whole example again but with the only difference being I didnt have a Primary Key on the base table, it seems to work so why does it not work if the base table has a Primary Key, it should still work according to Oracle documentation....
                  • 6. Re: materialized view not refreshing
                    672680
                    without primary key on base table, how did you create the create materialized view log?
                    by the change didn`t reflect in MV, do you mean the column COL1 or both COL1 & COL2?
                    I was just focusing on the COL2 which doesn`t seem work.
                    • 7. Re: materialized view not refreshing
                      619521
                      PhoenixBai ,

                      I just created a standard table and then created a MV on top of it, I did not specify the 'create materialized view log ....'
                      Eitherway getting back to the main issue, I've tried almost everything I can think off but it refuses to update the changes in col2 in the MV.
                      • 8. Re: materialized view not refreshing
                        user503699
                        sunny_sg wrote:
                        Folks,

                        Working on Oracle 11g, the problem that I have detailed below appears to show my MV is not refreshing, I am not sure why, maybe I missed a step, any hints much appreciated:-
                        I don't have 11g to test at the moment but till 10g it is not possible to change the source query for materialzed view (and certainly not automagically). At least, there is no documented way to do the same. ;)
                        BTW, there are couple of issues with your MV definition
                        1) You have explicitly mentioned only COL1 while creating materialzed view so the MV only "knows" about how to populate data for COL1.
                        2) You should use either REFRESH ON DEMAND or START WITH...NEXT but not both.

                        You can check the contents of QUERY column in ALL_MVIEWS data dictionary view to see that the query used for MV refresh is not affected when you add columns to base table and MV.
                        Your only option is to recreate the materialzed view.
                        • 9. Re: materialized view not refreshing
                          672680
                          Like I said, after adding the new column, using the sql below, you will find that your materialized view is still based on COL1 only, not COL1 & COL2.
                          Adding a new column COL2 to materialized view is not changing the definition of that MV.
                          Creating the MV based on SELECT * FROM t and then adding new column to base table will not change the original MV definition either.
                          select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MV') FROM DUAL;
                          The only way might be drop it and re-create it!
                          • 10. Re: materialized view not refreshing
                            user503699
                            PhoenixBai wrote:
                            Like I said, after adding the new column, using the sql below, you will find that your materialized view is still based on COL1 only, not COL1 & COL2.
                            Adding a new column COL2 to materialized view is not changing the definition of that MV.
                            select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MV') FROM DUAL;
                            On which version does that work? I am asking because I get an error on 10.2.0.4

                            Apologies. I was using 'MATERIALIZE VIEW' and not 'MATERIALZED_VIEW'

                            Edited by: user503699 on Sep 7, 2010 2:50 PM
                            • 11. Re: materialized view not refreshing
                              619521
                              user503699,

                              In reference to your points:-

                              1) I have also changed the MV DDL by using 'alter materialized view add columns col2 varchar2(20)' , and separately re-complied successfully.

                              2) I take your point about not using 'refresh on demand' with 'start ... next' etc, as I understand the 'start and next' will override the 'refresh on demand' anyway.

                              I am finding it hard to believe that I cannot after a explicit refresh (see my example) still not see the values in col2 in the mv.
                              • 12. Re: materialized view not refreshing
                                user503699
                                sunny_sg wrote:
                                user503699,

                                In reference to your points:-

                                1) I have also changed the MV DDL by using 'alter materialized view add columns col2 varchar2(20)' , and separately re-complied successfully.
                                I saw that but materialzed view is a separate object and the only link it has with base table(s) is the query which it uses to refresh itself. That query is defined only when you create the materialized view.
                                You should be using normal views for what you are expecting to see.
                                I am finding it hard to believe that I cannot after a explicit refresh (see my example) still not see the values in col2 in the mv.
                                Is there a particular reason why you want to do this and not recreate materialzed view?
                                • 13. Re: materialized view not refreshing
                                  619521
                                  We do not want to recreate the MV due to the massive volume of data and the time it would take to do this is not feasible hence why I thought fast refresh was the best option.
                                  • 14. Re: materialized view not refreshing
                                    user503699
                                    sunny_sg wrote:
                                    We do not want to recreate the MV due to the massive volume of data and the time it would take to do this is not feasible hence why I thought fast refresh was the best option.
                                    But since a materialzed view physically stores data, separately from the underlying table(s), any approach will still need (at least) updating entire materialzed view with the value for new column.
                                    1 2 Previous Next