Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 19 October 2014

Analyzing Materialized Views for Fast Refresh

The article will describe how materialized views can be analyzed and optimized to ensure they can be FAST REFRESHED, through this way refresh time will reduced from more than 5hours to less than 1hour. 
The tools used to perform the FAST REFRESH analysis are:
         DBMS_MVIEW
         MV_CAPABILITIES_TABLE
The Oracle provided DBMS_MVIEW.explain_mview procedure is used to analyze each of the existing materialized views FAST REFRESH capabilities and write the results to the MV_CAPABILITIES_TABLE.
The MV_CAPABILITIES TABLE is created in the owner’s schema by running the following Oracle 9i script:
SQL> @$ORACLE_HOME/rdbms/admin/utlxmv.sql

Steps:
        Created the necessary materialized view logs for each of the master tables making sure to include all columns referenced in any of the materialized view aggregations along with the ROWID, SEQUENCE, and INCLUDING NEW VALUES clauses based on the Oracle 9i documentation FAST REFRESH requirements.
        Created materialized views including the REFRESH FORCE ON DEMAND option, in order to prepare for the detailed analysis of the respective FAST REFRESH capabilities.
        Iterated through each existing materialized view analyzing and re-factoring as necessary to meet the specific FAST REFRESH requirements using the contents of the MV_CAPABILITIES_TABLE MSGTXT column as based on the oracle 9i guide.

Truncated the MV_CAPABILITIES_TABLE to remove any prior analysis written to the table by the DBMS_MVIEW.explain_mview procedure.
SQL> TRUNCATE TABLE hrms.mv_capabilities_table;

Executed dbms_mview.explain_mview() procedure to explain the materialized view:
SQL> EXEC dbms_mview.explain_mview(‘hrms.payroll_MV’);

To check MV_CAPABILITIES_TABLE for each FAST REFRESH capability:
SQL> SELECT capability_name, possible, substr(msgtxt,1,60) AS msgtxt
     FROM hrms.mv_capabilities_table
     WHERE capability_name like '%FAST%';

Examples: To Create FAST_REFRESH materialized view:
SQL> CREATE MATERIALIZED VIEW hrms.payroll_v_MV
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
select * from payroll_v
/
SQL> Truncate table mv_capabilities_table;
SQL> exec dbms_mview.explain_mview('hrms.payroll_v_mv');

SQL> SELECT capability_name, possible, SUBSTR(msgtxt,1,60) AS msgtxt
           FROM hrms.mv_capabilities_table
           WHERE capability_name like '%FAST%';

SQL> DROP MATERIALIZED VIEW hrms.payroll_v_MV;

SQL> CREATE MATERIALIZED VIEW hrms.payment_master_v_MV
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
select * from payment_master;

SQL> TRUNCATE TABLE mv_capabilities_table;
SQL> EXEC dbms_mview.explain_mview('hrms.payment_master_v_mv');

SQL> SELECT capability_name,  possible, SUBSTR(msgtxt,1,60) AS msgtxt
           FROM mv_capabilities_table
           WHERE capability_name like '%FAST%';

Example: Aggregation of Materialized view with required M_Veiw log
SQL> CREATE MATERIALIZED VIEW LOG ON hrms.payroll
WITH SEQUENCE, ROWID (EMPLOYEE_NUMBER, DEPT_CODE, BANK_TRANSFER, CASH_TRANSFER, OTHERS_TRANSFER)
INCLUDING NEW VALUES;

SQL> CREATE MATERIALIZED VIEW LOG ON hrms.payment_master
WITH SEQUENCE, ROWID (EMPLOYEE_NUMBER)
INCLUDING NEW VALUES;

SQL> CREATE MATERIALIZED VIEW hrms.salary_mv
           NOLOGGING
           PARALLEL
           BUILD IMMEDIATE
           REFRESH FORCE ON DEMAND
           ENABLE QUERY REWRITE
           AS
           SELECT e.employee_number, e.employee_name, d.payslip_number,
           d. payslip_date, d.sum(Amount)
           FROM payment_master e, pay_payment_detail d
           WHERE e.employee_number=d.employee_number
           and d.payslip_date > ’31-SEP-2014’;

           GROUP BY e.employee_number;

16 comments:

  1. Really something Grate in this article Thanks for sharing this. We are providing Online Training Classes. After reading this slightly I am changed my way of introduction about my training to people.
    ORACLE DBA ONLINE TRAINING

    ReplyDelete
  2. Good explaination. For Oracle DBA training contact +919700204470

    ReplyDelete
  3. I cant wait to check out some of these blogs! I’ve really wanted to start learning more about cars and auto repairs lately and I think this will help a lot. I think it can save my family some money if we knew how to do some repairs at home.! Thanks again for all the options.

    bike spa services in mumbai
    house cleaning services in mumbai
    car wash services in mumbai

    ReplyDelete
  4. Awesome resources!
    Thanks for sharing such good article.

    Best Consultancy in Bangalore

    ReplyDelete
  5. Great post.
    It really helped me to learn something new. So thanks.

    Software Comapny In Bangalore

    ReplyDelete
  6. Awesome resources!
    Thanks for sharing such good article. This is really helpful.

    cladding tiles
    Kitchen Tiles
    Highlighter Tiles

    ReplyDelete
  7. This blog is very useful to us. Thank you for sharing these information on Oracle DBA online course.

    Oracle DBA online training in hyderabad
    Oracle DBA online course in hyderabad

    ReplyDelete
  8. This post is very informative for everybody. I would like to appreciate your work for good accuracy and got informative knowledge from here.
    Oracle Fusion financial

    ReplyDelete
  9. If you want your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (no matter why you broke up) you got to watch this video
    right away...

    (VIDEO) Get your ex CRAWLING back to you...?

    ReplyDelete