create the mview with fast refresh - the lazy way

in 10g before creating an complex mview , thinking through how and is it possible to create the mview with refresh fast on commit enable qurey rewrite funtion?

remember count(*) is the key for any mview's fast refresh when there is a need for any of group by and other aggregate funtion.

if still hard to decide how to create the mview especially the mview log . use the package dbms_advisor.tune_mview  before anything's creation:

the only important thing should bare in mind is the key words to create an mview :

create mview xxx refresh fast on commit enable qurey rewrite as xxxx ; then the advisor can tell you all you neededBEFORE YOU EVEN HAVE THE MATERIALIZED VIEW LOG. you then can just copy and paste the creating mivew log statement . 

another note should bare in mind is that you should add as many as possible count(expr ) in the select statement :

 

here i have a table T

SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 ID                         NUMBER
 VALUE                      NUMBER
 NAME                       VARCHAR2(10)

I want to build up a mview to support query rewrite , refresh fast on commit for the query the sum value  grouped by id number;

 insert into t select FILE_ID,BYTES,substr(FILE_NAME,1,10) from dba_data_files;

insert into t select * from t; and execute the statement many times. now many rows in table T.

select sum(value) ,id from t group by id   is the statement will in my application ,how I build a mview and enable the features ?

create materialized view mv_t refresh fast on commit enable query rewrite as select count(*),count(value),sum(value),count(id),id from t group by id;

declare
    str varchar2(30);
    begin
    dbms_advisor.tune_mview(str,'create materialized view mv_t refresh fast on commit enable query rewrite as select count(*),count(value),sum(value),count(id),id from t group by id');
    dbms_output.put_line(str);
end;

 

SQL> select STATEMENT from dba_tune_mview where task_name='TASK_291' order by action_id;

STATEMENT
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW LOG ON "QDL"."T" WITH ROWID, SEQUENCE ("ID","VALUE")  I
NCLUDING NEW VALUES

ALTER MATERIALIZED VIEW LOG FORCE ON "QDL"."T" ADD ROWID, SEQUENCE ("ID","VALUE"
)  INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW QDL.MV_T   REFRESH FAST WITH ROWID ENABLE QUERY REWRITE
 AS SELECT QDL.T.ID C1, COUNT("QDL"."T"."ID") M1, SUM("QDL"."T"."VALUE") M2, COU
NT("QDL"."T"."VALUE") M3, COUNT(*) M4 FROM QDL.T GROUP BY QDL.T.ID

DROP MATERIALIZED VIEW QDL.MV_T

 

after execute  the statement for creating mview log and mview , clearly query rewrite works and refresh fast on commit works.

SQL> set autot on
sSQL> elect sum(value) from t group by id;

SUM(VALUE)
----------
4.4668E+13
       200
2.7488E+13
2.7488E+12
4.4668E+13

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=5 Bytes=65)
   1    0   MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_T' (MAT_VIEW REWRITE
          ) (Cost=3 Card=5 Bytes=65)

 

 

 

when there is an group by , mview log with a rowid  is a must
sequence should including all the grouped by columns  
including new values store both the before image and after image for one transaction eg(update one row , we see 2 rows in m$ table) it can help oracle to fast refresh the mview when there is a group by