CODEWITHSUNDEEP
oracle-database
tooba jalali
tooba jalali

Reputation: 86

Oracle Materialized view

According to the oracle documents, we can not use fast refresh method for refreshing aggregate materialized view. I found this example in Oracle documents: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm

CREATE MATERIALIZED VIEW LOG ON times
   WITH ROWID, SEQUENCE (time_id, calendar_year)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON products
   WITH ROWID, SEQUENCE (prod_id)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW sales_mv 
   BUILD IMMEDIATE 
   REFRESH FAST ON COMMIT 
   AS  
   SELECT t.calendar_year, p.prod_id, 
      SUM(s.amount_sold) AS sum_sales
       FROM times t, products p, sales s
      WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
      GROUP BY t.calendar_year, p.prod_id;

every time which I tried to use aggregations and fast refresh with each other I got error.
Is there any special tips in case of using fast refresh and aggregation functions with each other?

Kind Regards

Upvotes: 3

Views: 236

Answers (1)

tooba jalali
tooba jalali

Reputation: 86

According to my survey, for creating MV with aggregation functions and fast refresh method ,your MV and MV log should have special strucures,for seeing correct structures of MV and MV log run below scripts:

begin
 dbms_advisor.tune_mview(task_name=>:t,
 mv_create_stmt=>'create materialized view mv1 refresh fast 
as select job,sum(sal)    from emp group by job');
end;

then execute below query to see the desired structures for MV and MV logs:

select  dbms_lob.substr( statement, 4000, 1 ), statement from user_tune_mview
where task_name='TASK_2042' order by action_id;

Upvotes: 1

Related Questions