10 Replies Latest reply: Jul 10, 2009 3:42 PM by SeánMacGC RSS

    Materialized View Log options

    Catfive Lander
      I have read the documentation. I know you can specify options such as 'with primary key' and/or 'with rowid' when creating a materialized view log. Also, that you can specify to include additional columns in the log -and that these can then be seen in the MLOG$ table that gets created. But I don't know why or in what circumstances you'd specify the one or the other, or include columns or not. Can anyone give some example scenarios where you'd do one or the other?
        • 1. Re: Materialized View Log options
          SeánMacGC
          Hello again,

          The fundamental reason (why you would specify primary key and/or ROWID) is to facilitate fast refreshes (as opposed to a complete refresh) of the materialized view -- a lot will depend on the MV query itself, and whether one or both of primary key & ROWID are needed, i.e., if there's a COUNT(*) involved, where the primary key would play a role, or without such an aggregate, the ROWID will be necessary (in any case) for fast refresh.

          Edited by: SeánMacGC on Jul 9, 2009 5:54 PM

          If you run the utlxmv.sql script in $ORACLE_HOME/rdbms/admin, that will create the MV_CAPABILITIES_TABLE, which when you create an MV and execute DBMS_MVIEW.EXPLAIN_MVIEW('your_mview') will populate that with what that MV is capable of, i.e.,
          SQL> desc mv_capabilities_table;
           Name                                      Null?    Type
           ----------------------------------------- -------- ---------------------------
          
           STATEMENT_ID                                       VARCHAR2(30)
           MVOWNER                                            VARCHAR2(30)
           MVNAME                                             VARCHAR2(30)
           CAPABILITY_NAME                                    VARCHAR2(30)
           POSSIBLE                                           CHAR(1)
           RELATED_TEXT                                       VARCHAR2(2000)
           RELATED_NUM                                        NUMBER
           MSGNO                                              NUMBER(38)
           MSGTXT                                             VARCHAR2(2000)
           SEQ                                                NUMBER
          
          SQL> select capability_name, possible from mv_capabilities_table;
          
          CAPABILITY_NAME                P
          ------------------------------ -
          PCT                            N
          REFRESH_COMPLETE               Y
          REFRESH_FAST                   N
          REWRITE                        N
          PCT_TABLE                      N
          REFRESH_FAST_AFTER_INSERT      N
          REFRESH_FAST_AFTER_ONETAB_DML  N
          REFRESH_FAST_AFTER_ANY_DML     N
          REFRESH_FAST_PCT               N
          REWRITE_FULL_TEXT_MATCH        N
          REWRITE_FULL_TEXT_MATCH        N
          REWRITE_PARTIAL_TEXT_MATCH     N
          REWRITE_PARTIAL_TEXT_MATCH     N
          REWRITE_GENERAL                N
          REWRITE_GENERAL                N
          REWRITE_PCT                    N
          PCT_TABLE_REWRITE              N
          • 2. Re: Materialized View Log options
            Catfive Lander
            I'm sorry if I'm misinterpreting you. You seem to be saying that if I don't specify 'with rowid', and only specify 'with primary key', that my MV won't fast refresh?

            But:
            SQL> create table t1 (cola number primary key, colb varchar2(5));
            Table created.
            
            SQL> create materialized view log on t1 with primary key;
            Materialized view log created.
            
            SQL> create materialized view mv1 refresh fast on demand as select * from t1;
            Materialized view created.
            
            SQL> exec dbms_mview.explain_mview('MV1');
            PL/SQL procedure successfully completed.
            
            SQL> select capability_name, possible,
              2         substr(related_text,1,8), substr(msgtxt, 1, 60)
              3  from mv_capabilities_table
              4  order by seq;
            
            CAPABILITY_NAME                P SUBSTR(R SUBSTR(MSGTXT,1,60)
            ------------------------------ - -------- ------------------------------------------------------------
            PCT                            N
            REFRESH_COMPLETE               Y
            REFRESH_FAST                   Y
            REWRITE                        N
            PCT_TABLE                      N Query re Oracle error: see RELATED_NUM and RELATED_TEXT for details
            REFRESH_FAST_AFTER_INSERT      Y
            REFRESH_FAST_AFTER_ONETAB_DML  Y
            REFRESH_FAST_AFTER_ANY_DML     Y
            REFRESH_FAST_PCT               N          PCT is not possible on any of the detail tables in the mater
            REWRITE_FULL_TEXT_MATCH        N Query re Oracle error: see RELATED_NUM and RELATED_TEXT for details
            REWRITE_FULL_TEXT_MATCH        N          query rewrite is disabled on the materialized view
            REWRITE_PARTIAL_TEXT_MATCH     N          materialized view cannot support any type of query rewrite
            REWRITE_PARTIAL_TEXT_MATCH     N          query rewrite is disabled on the materialized view
            REWRITE_GENERAL                N          materialized view cannot support any type of query rewrite
            REWRITE_GENERAL                N          query rewrite is disabled on the materialized view
            REWRITE_PCT                    N          general rewrite is not possible or PCT is not possible on an
            PCT_TABLE_REWRITE              N Query re Oracle error: see RELATED_NUM and RELATED_TEXT for details
            
            17 rows selected.
            SQL>
            ...and that seems to say I can do a fast refresh, even though I created a primary key MV Log, without the rowid.

            Anyway, suppose I'm simply replicating a table, so my MV is created as a select * from table@remote_db, and that needs to fast refresh. Would I need 'with primary key'? Would I need 'with rowid'? Or would I need both? (This is 10.2.0.4, by the way, if that makes any difference).
            • 3. Re: Materialized View Log options
              26741
              If you have a PRIMARY KEY, you don't need WITH ROWID.

              If you don't have a PRIMARY KEY, you have to add WITH ROWID.

              Also see "General Restrictions on Fast Refresh" , "Restrictions on Fast Refresh on Materialized Views with Joins Only" and "Restrictions on Fast Refresh on Materialized Views with Aggregates" and "Restrictions on Fast Refresh on Materialized Views with UNION ALL"

              at http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm
              • 4. Re: Materialized View Log options
                Catfive Lander
                If you have a PRIMARY KEY, you don't need WITH ROWID. If you don't have a PRIMARY KEY, you have to add WITH ROWID.

                Right, that's what I thought and what the evidence suggests, but it wasn't what (I think) was posted by SeánMacGC (though, in fairness, I may have misunderstood what he was saying).

                In any case, that still leaves my original question unanswered: when/why would you specify rowid instead of primary key? When/why would you specify primary key rather than rowid? If it's one or the other, why is there a choice in the first place? What functionality differences arise from making the choice? Are there any?? Are you implying by your link to the documentation regarding fast refresh restrictions that complex MVs, using select statements with complex aggregation and joins etc, will refresh fast or not depending on the choice? If so, does that mean that if my MV is simply a 'select * from my_table@remote_db', no joins and no aggregation, that I don't have to worry about which choice to make?
                • 5. Re: Materialized View Log options
                  26741
                  There are lots of schemas which do not define Primary Key constraints in the database.

                  The WITH ROWID was implemented to support such schemas.
                  • 6. Re: Materialized View Log options
                    Catfive Lander
                    So, you're saying 'always use Primary Key' if the table you're building the MV on has a primary key? That it's not really a choice you'd ever have to think about, because you'd only ever use one in the 'I have a primary key' scenario, and the other when you don't have a primary key?

                    That certainly makes sense if so!

                    Can you then explain why one would ever want/need to include additional columns in the materialized view log? Is that again something to do with making fast refresh work in circumstances it otherwise wouldn't? So that if you're doing a simple select * from table type of MV, you'd never need to include any additional columns?
                    • 7. Re: Materialized View Log options
                      26741
                      I guess that it isn't explained well at http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#sthref545


                      But see the documentation on the CREATE MATERIALIZED VIEW LOG statement

                      http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6003.htm#i2064649
                      Use the WITH clause to indicate whether the materialized view log should record the primary key, rowid, object ID, or a combination of these row identifiers when rows in the master are changed. You can also use this clause to add a sequence to the materialized view log to provide additional ordering information for its records.

                      This clause also specifies whether the materialized view log records additional columns that might be referenced as filter columns, which are non-primary-key columns referenced by subquery materialized views, or join columns, which are non-primary-key columns that define a join in the subquery WHERE clause.
                      • 8. Re: Materialized View Log options
                        Catfive Lander
                        I don't think it is explained particularly well, and that's the main reason I'm asking!

                        The quotation from the documentation you give simply re-opens my confusion about rowid/primary key. If you use primary key when the base table has a primary key, and rowid when it doesn't, when would you ever use "a combination" of these things?

                        Sorry, but my question remains fundamentally unanswered (and I have read all the documentation you link to: I'm still none the wiser).

                        If the answer is "you use whatever makes your MV fast refresh" or "whatever makes your MV fast refresh most efficiently", I'd buy that. But I just wish someone would say it, in words of not too many syllables!

                        Put it another way: I created an MV "with primary key"; it fast refreshes just fine. Should I be in the least bit worried that I'm missing out on some lovely piece of functionality because I didn't add rowid and six other columns into the log??
                        • 9. Re: Materialized View Log options
                          26741
                          Say you have an MView which is a query on 3 tables, each table having an MV Log.

                          Your MV query is a join of 3 tables. Wouldn't it be nice to have the join columns available in the MV Logs of the 3 tables so that your MV query can be executed better ?

                          On the other hand, if you have an MV on a single table, you don't really need to add any columns to the MV Log, because you will be hitting the single table for the columns. (However, if you have a table with 30 columns and an MV with only 4 columns, you might have created a the MV Log with 4 columns so that your MV refresh doesn't hit the table).
                          • 10. Re: Materialized View Log options
                            SeánMacGC
                            Catfive Lander wrote:
                            If you have a PRIMARY KEY, you don't need WITH ROWID. If you don't have a PRIMARY KEY, you have to add WITH ROWID.

                            Right, that's what I thought and what the evidence suggests, but it wasn't what (I think) was posted by SeánMacGC (though, in fairness, I may have misunderstood what he was saying).
                            Yeah, sorry, that's what I meant: it all depends on your query and the tables involved as to whether you needed to include only primary keys, ROWIDs, or both (as Hemant says).