DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, September 11, 2015

STEPS TO REFRESH THE MATERIALIZED VIEW IN FAST REFRESH MODE WHICH REFRESHES IN COMPLETE REFRESH USING DBMS TUNING ADVISER


1) Materialized view that refreshes in  COMPLETE Refresh Mode
-----------------------------------------------------------------------------------------------

OZF_FUNDS_ALL_B 


2) Manually testing the MV refresh in FAST mode: 
-------------------------------------------------------------------------

BEGIN
   dbms_mview.refresh('OZF_FUNDS_ALL_B','F');
END;

ERROR Message :
----------------------------

BEGIN
   dbms_mview.refresh('OZF_FUNDS_ALL_B','F');
END;
Error at line 1
ORA-12004: REFRESH FAST cannot be used for materialized view "THERMOS"."OZF_FUNDS_ALL_B"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at line 2
Script Terminated on line 21.


3) Take the script of the materialized veiw OZF_FUNDS_ALL_B and run the DBMS tuning advisor as below shown. Replace your MV query in the red marked area.

Running DBMS Tuning Advisor for the MVs :
------------------------------------------------------
vari t varchar2(50)
begin
dbms_advisor.tune_mview(task_name=>:t,
                         mv_create_stmt=>'CREATE MATERIALIZED VIEW cust_mv BUILD IMMEDIATE REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT org_id, creation_date, fund_id, parent_fund_id, fund_number, fund_type,status_code, category_id, start_date_active, end_date_active, liability_flag, original_budget, transfered_in_amt, transfered_out_amt, attribute2, attribute3, attribute4, attribute5, plan_id FROM ozf_funds_all_b_bkp');
end;
/

PL/SQL procedure successfully completed.


4) Fetch the rebuilded query as show below:
----------------------------------------------------------------

SQL> set long 5000
SQL> select statement from user_tune_mview where task_name=:t order by action_id;


STATEMENT
---------------------
CREATE MATERIALIZED VIEW LOG ON "THERMOS"."OZF_FUNDS_ALL_B_BKP" WITH ROWID
ALTER MATERIALIZED VIEW LOG FORCE ON "THERMOS"."OZF_FUNDS_ALL_B_BKP" ADD ROWID
CREATE MATERIALIZED VIEW THERMOS.OZF_FUNDS_ALL_B BUILD IMMEDIATE  REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT org_id, creation_date, fund_id, parent_fund_id, fund_number, fund_type,status_code, category_id, start_date_active, end_date_active, liability_flag, original_budget, transfered_in_amt, transfered_out_amt, attribute2, attribute3, attribute4, attribute5, plan_id FROM ozf_funds_all_b_bkp
DROP MATERIALIZED VIEW THERMOS.CUST_MV


5) Now run the query one by one as show below:

The tuning adviser suggested to create MV log for the base tables that are accessed by the Materialized views.

a)  Drop the old materialized view.  
b)  Create materialized view log for the base tables.
c)  Re-create the materialized view as tuning adviser suggested.
d)  Refresh the MV using FAST option.  


SQL> CREATE MATERIALIZED VIEW LOG ON "THERMOS"."OZF_FUNDS_ALL_B_BKP" WITH ROWID;

Materialized view log created.


SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "THERMOS"."OZF_FUNDS_ALL_B_BKP" ADD ROWID;

Materialized view log altered.


SQL> CREATE MATERIALIZED VIEW THERMOS.OZF_FUNDS_ALL_B IMMEDIATE  REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT org_id, creation_date, fund_id, parent_fund_id, fund_number, fund_type,status_code, category_id, start_date_active, end_date_active, liability_flag, original_budget, transfered_in_amt, transfered_out_amt,attribute2, attribute3, attribute4, attribute5, plan_id FROM ozf_funds_all_b_bkp;

Materialized view created.


6) Now Test it by refreshing in FAST option :
-----------------------------------------------------------------

SQL> BEGIN
   2  dbms_mview.refresh('OZF_FUNDS_ALL_B','F');
   3  END;
   4  /

PL/SQL procedure successfully completed.

===============================Thank you================================                                                          

No comments: