1 2 Previous Next 25 Replies Latest reply on Aug 10, 2015 11:37 AM by Sven W. Go to original post
      • 15. Re: Materialized views refresh mode and refresh option
        Sven W.

        Caglar wrote:

        ...

        Also, I see that we use DBMS_SNAPSHOT.REFRESH procedure in order to refresh MVs. Is it deprecated? Should we use DBMS_MVIEW package in order to refresh MVs?

         

        If so, should we have to run the utlxrw.sql script in order to use DBMS_MVIEW? How can ı understand that DBMS_MVIEW is created?

         

        Thanks

        DBMS_MVIEW is the new name. DBMS_SNAPSHOT is the old name. 

        The name DBMS_SNAPSHOT is deprecated, but you can still use it.

         

        DBMS_MVIEW in fact is just a synonym onto the DBMS_SNAPSHOT package.

        So no you don'tt need to run any install script. You can simply use one or the other name.

        For new code I would always choose the newer name, however there is no harm in still using the old name dbms_snapshot.

        1 person found this helpful
        • 16. Re: Materialized views refresh mode and refresh option

          As far as I know, we have to run utlxmv.sql script in order to use DBMS_MVIEW.EXPLAIN_MVIEW procedure.

           

          http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm

          Note that you must run the utlxmv.sql script prior to calling EXPLAIN_MVIEW except when you are placing the results in MSG_ARRAY. The script is found in the admin directory. It is to create theMV_CAPABILITIES_TABLE in the current schema. An explanation of the various capabilities is in Table 8-7, and all the possible messages are listed in Table 8-8.



          In addition, What is the fastest way do you think to refresh COMPLEX type MV?


          Regards

          • 17. Re: Materialized views refresh mode and refresh option
            John Stegeman

            The documentation tells you that complex views are not fast refreshable, so there is no "fastest" way - there is the ONLY way. COMPLETE

            • 18. Re: Materialized views refresh mode and refresh option

              John Stegeman wrote:

               

              The documentation tells you that complex views are not fast refreshable, so there is no "fastest" way - there is the ONLY way. COMPLETE

               

              I just thought that maybe you experts have some tricky way to share

              • 20. Re: Re: Materialized views refresh mode and refresh option
                Sven W.

                There are two ways to run dbms_mview.explain_mview. For one way the table is needed. but you can also choose the second way with the Varray.

                 

                Here is an example how you can run it.

                 

                First create a helper function

                 

                create or replace
                function explainMview (p_mview in varchar2) return SYS.ExplainMVArrayType pipelined
                authid current_user
                as
                  pragma autonomous_transaction;
                  v_expl SYS.ExplainMVArrayType;
                  v_msg  SYS.ExplainMVMessage;
                  v_ind  binary_integer;
                begin
                  dbms_mview.explain_mview(mv => p_mview, msg_array => v_expl);
                  commit;
                
                  v_ind := v_expl.first;
                  loop
                    v_msg := v_expl(v_ind);
                    pipe row (v_msg);
                
                    exit when v_ind >= v_expl.last;
                    v_ind := v_expl.next(v_ind);
                  end loop;
                
                end explainMview;
                /
                
                
                

                Then run a select on this pipelined table function.

                 

                select * from table(isdba.explainMview('YOURSCHEMA.YOURMVIEW'));
                
                
                

                 

                I run the function usually with DBA privs.

                And give execute rights to any interested schemas, so that I can do the explain from that schema.

                 

                Will not work in 10g.

                1 person found this helpful
                • 22. Re: Materialized views refresh mode and refresh option

                  One more question, Do you think using paralelism more than 0 make faster resresh?

                   

                   

                  DBMS_SNAPSHOT.REFRESH(

                                                  LIST                 => 'MV_M'

                                                 ,METHOD               => '?'

                                                 ,PUSH_DEFERRED_RPC    => TRUE

                                                 ,REFRESH_AFTER_ERRORS => FALSE

                                                 ,PURGE_OPTION         => 1

                                                 ,PARALLELISM          => 4

                                                 ,ATOMIC_REFRESH       => TRUE

                                                 ,NESTED               => FALSE);

                  • 23. Re: Materialized views refresh mode and refresh option
                    John Stegeman

                    It doesn't matter what I think, what matters is reality, and that will depend on your servers, the number of CPUs, the I/O bandwidth, the user activity at the time, etc. You will find out when you test which one is faster.

                    • 24. Re: Materialized views refresh mode and refresh option

                      Sven,

                       

                      This is exactly what I am looking for! Thanks for your magnificent support and time!

                       

                      I just want to ask one more question about the sytax. Don't we have to write RETURN keyword at the end of the function? In addition why we use COMMIT, is there any transaction happen? As far as I see, we only call a procedure which populate a varray.

                       

                      Thanks again.

                      Regards

                      • 25. Re: Materialized views refresh mode and refresh option
                        Sven W.

                        Caglar wrote:

                         

                        Sven,

                         

                        This is exactly what I am looking for! Thanks for your magnificent support and time!

                         

                        I just want to ask one more question about the sytax. Don't we have to write RETURN keyword at the end of the function? In addition why we use COMMIT, is there any transaction happen? As far as I see, we only call a procedure which populate a varray.

                         

                        Thanks again.

                        Regards

                        The code I posted is a special case. It is a pipelined table function. The rows are RETURNed using the PIPE ROW keyword. Therefore no return statement at the end is needed. I think it doesn't hurt if you add one thou. The table operator in the select then fetches the rows from the function and gives us the nice column+row representation from the matching object types.

                         

                        The commit is needed because I run this as a autonomous transaction. This means it runs independently from the main transaction. The dbms_mview.explain_mview call is requiring or doing a transaction. I think the reason has to do with its overloaded version which writes into a database table.

                        1 2 Previous Next