This discussion is archived
7 Replies Latest reply: Sep 4, 2012 7:09 AM by user275290 RSS

MV fast Refresh on commit

user275290 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points