7 Replies Latest reply: Sep 4, 2012 9:09 AM by user275290 RSS

    MV fast Refresh on commit

    user275290
      Hi Gurus,

      11.2.0.1

      AIX 6.1

      I have create Materialized view with on commit refresh,i have created MV logs on the base tables of the MV with rowid and sequence options.

      When i insert or update any of the base table the commit is taking around 2 mins,how can i speed up the commits?

      CREATE MATERIALIZED VIEW XMATAPP.MV_CUST_SEARCH NOLOGGING PARALLEL 8 BUILD IMMEDIATE 
      REFRESH FAST ON COMMIT  AS
           SELECT personprofilenames.pp_legalentity_number legal_entity_number,
                personprofilenames.first_name,
                personprofilenames.last_name,
                personprofilenames.middle_name,
                personprofile.dob,
                personprofilenames.name_type_id name_type,
                personprofile.eye_color_id,
                personprofilenames.suffix_value suffix_value,
                personprofile.gender_id gender1,
                personprofilenames.is_deleted ,
                refgender.description gender,
                refeyecolors.description eyecolor ,
                personprofilenames.rowid r1 ,
                refgender.rowid r2,
                refeyecolors.rowid r3,
                personprofile.rowid r4
               FROM 
               person_profile personprofile, 
               person_profile_names personprofilenames,
               ref_gender refgender,
               ref_eye_colors refeyecolors 
              WHERE personprofile.pp_legalentity_number = personprofilenames.pp_legalentity_number -- INNER JOIN 
                  AND personprofile.gender_id = refgender.id(+)
                  AND personprofile.eye_color_id = refeyecolors.id(+)
                 AND personprofile.is_deleted   = 0
                      
           
        create index xmatapp.idx_fname_mv_cust_search on xmatapp.mv_CUST_SEARCH(first_name) nologging parallel 8;
          
      create index xmatapp.idx_lname_mv_cust_search on xmatapp.mv_CUST_SEARCH(last_name) nologging parallel 8;
      
      create index xmatapp.idx_lname_dob_mv_cust_search on xmatapp.mv_CUST_SEARCH(last_name,dob) nologging parallel 8;
      
      create index xmatapp.idx_lname_gen_mv_cust_search on xmatapp.mv_CUST_SEARCH(last_name,gender1) nologging parallel 8;
      
      
      
      
      CREATE MATERIALIZED VIEW LOG ON XMATAPP.ref_gender WITH rowid,sequence;
      
      CREATE MATERIALIZED VIEW LOG ON XMATAPP.ref_eye_colors WITH rowid,sequence;
      
      CREATE MATERIALIZED VIEW LOG ON XMATAPP.PERSON_PROFILE_NAMES WITH rowid,sequence;
      
      CREATE MATERIALIZED VIEW LOG ON XMATAPP.PERSON_PROFILE WITH rowid,sequence;
      Should creating indexes on rowid increase the perofrmance of refresh and commit time?

      Thanks

      Edited by: Monto on Aug 29, 2012 6:54 PM
        • 1. Re: MV fast Refresh on commit
          spajdy
          Maybe indexes on rowid columns help you.

          create index xmatapp.idx_r1_mv_cust_search on xmatapp.mv_CUST_SEARCH (r1);
          create index xmatapp.idx_r2_mv_cust_search on xmatapp.mv_CUST_SEARCH (r2);
          create index xmatapp.idx_r3_mv_cust_search on xmatapp.mv_CUST_SEARCH (r3);
          create index xmatapp.idx_r4_mv_cust_search on xmatapp.mv_CUST_SEARCH (r4);
          • 2. Re: MV fast Refresh on commit
            user275290
            Thanks.I'm not able to create the MV in another database whose version is 11.2.0.3(AIX 6.1) ,same create statement.Created the MV logs in the schema since it own the base tables as well as MV.

            During MV creation throwing error ORA-12018

            ORA-12018: following error encountered during code generation for "XMATSBX"."MV_CUST_SEARCH"
            ORA-00904: : invalid identifier



            Here is the code.
            CREATE MATERIALIZED VIEW XMATSBX.MV_CUST_SEARCH NOLOGGING PARALLEL 8 BUILD IMMEDIATE
             REFRESH FAST ON COMMIT AS
                ( SELECT personprofilenames.pp_legalentity_number legal_entity_number,
                      personprofilenames.first_name,
                      personprofilenames.last_name,
                      personprofilenames.middle_name,
                      personprofile.dob,
                      personprofilenames.name_type_id name_type,
                      personprofile.eye_color_id,
                      personprofilenames.suffix_value suffix_value,
                      personprofile.gender_id gender1,
                      personprofilenames.is_deleted ,
                      refgender.description gender,
                      refeyecolors.description eyecolor ,
                      personprofilenames.ROWID as r1 ,
                      refgender.ROWID as r2,
                      refeyecolors.ROWID as  r3,
                      personprofile.ROWID as r4
                     FROM
                     person_profile personprofile,
                     person_profile_names personprofilenames,
                     ref_gender refgender,
                     ref_eye_colors refeyecolors
                    WHERE personprofile.pp_legalentity_number = personprofilenames.pp_legalentity_number
                             AND personprofile.gender_id = refgender.id(+)
                        AND personprofile.eye_color_id = refeyecolors.id(+) AND personprofile.is_deleted   = 0 )
            
            CREATE MATERIALIZED VIEW LOG ON XMATSBX.ref_gender WITH rowid,sequence,primary key ,commit scn INCLUDING NEW VALUES;
            
            CREATE MATERIALIZED VIEW LOG ON XMATSBX.ref_eye_colors WITH rowid,sequence,primary key ,commit scn INCLUDING NEW VALUES;
            
            CREATE MATERIALIZED VIEW LOG ON XMATSBX.PERSON_PROFILE_names WITH rowid,sequence,primary key ,commit scn INCLUDING NEW VALUES;
            
            create MATERIALIZED VIEW LOG ON XMATSBX.PERSON_PROFILE WITH rowid,sequence,primary key ,commit scn INCLUDING NEW VALUES;
            Privileges of the user.
            USERNAME     PRIVILEGE     ADMIN_OPTION
            
            XMATSBX     QUERY REWRITE     NO
            XMATSBX     CREATE SESSION     NO
            XMATSBX     ON COMMIT REFRESH     NO
            XMATSBX     CREATE MATERIALIZED VIEW     NO
            XMATSBX     FORCE ANY TRANSACTION     NO
            XMATSBX     CREATE TABLE     NO
            XMATSBX     CREATE PUBLIC SYNONYM     NO
            XMATSBX     UNLIMITED TABLESPACE     NO
            XMATSBX     CREATE TRIGGER     NO
            XMATSBX     CREATE DATABASE LINK     NO
            XMATSBX     CREATE SYNONYM     NO
            XMATSBX     SELECT ANY TABLE     NO
            XMATSBX     CREATE PROCEDURE     NO
            XMATSBX     ADVISOR     NO
            XMATSBX     CREATE SEQUENCE     NO
            XMATSBX     DROP PUBLIC SYNONYM     NO
            XMATSBX     CREATE TYPE     NO
            XMATSBX     CREATE VIEW     NO
            Not sure what prvilege am i missing here or hitting any bug?

            Thanks
            • 3. Re: MV fast Refresh on commit
              spajdy
              ORA-00904: : invalid identifier
              Did you check column names you use in select statement ? May be you make some typo error, please check taht column exists in base table.
              • 4. Re: MV fast Refresh on commit
                user275290
                Thanks.If i just do select then the query run returns resultset.Its just when i try to create MV its throwing error.

                Regards
                • 5. Re: MV fast Refresh on commit
                  spajdy
                  Interesting.
                  So try this:
                  1/ use alias for each column c1, c2, ..., cN
                  2/ try to use different name of matrialized view
                  • 6. Re: MV fast Refresh on commit
                    AdamMartin
                    Do you really need to do a refresh on commit? Could you just do a fast refresh at regular intervals?
                    • 7. Re: MV fast Refresh on commit
                      user275290
                      Yes since the data needs to be real time in sync with the base tables.The issue got resolved with the MV creation,the base tables had FGA policiies defined on them after dropping them the MV got created successfully.

                      Thanks