Oracle Database - MV Fast refresh, referencing view

Question

I got few questions about creating materialized views with fast refresh. The problem is I need to have in the query count(*) function, which is illegal for fast refresh. I read somewhere that I make an "assistance" view, which will execute count(). I want to reference that view from my Materialized View. When it comes to refresh, the complete is working perfectly, but during trying execute fast, the ORA 12004 error appear. I know I need a materialized view logs from tables I am referencing, but how about views? How to create them? Is it also illegal to use count() in referenced view? how to make a ROWID column for that view? If its illegal to use count() is there any other way despite creating the table to make it?

Answer 1

You most certainly can have count in a fast refresh materialized view:

create table t (
  c1 int primary key,
  c2 int
);

insert into t 
  select level, mod ( level, 3 )  
  from   dual
  connect by level <= 10;

commit;

create materialized view log on t
  with rowid, primary key ( c2 )
  including new values;

create materialized view mv
refresh fast on commit 
as 
  select c2, count (*)
  from   t
  group  by c2;

select * from mv;

C2     COUNT(*)   
    1           4 
    2           3 
    0           3 

insert into t 
  select -level, mod ( level, 3 )  
  from   dual
  connect by level <= 10;

commit;

select * from mv;

C2     COUNT(*)   
    1           8 
    2           6 
    0           6 

If you're not able to make a materilized view fast refreshable and you're not sure why, check out dbms_mview.explain_mview. This gives a breakdown of the possible refreshes and if they're possible for an MV.

For ones that aren't possible, there's a short explanation why:

create table mv_capabilities_table (
  statement_id      varchar(30) ,
  mvowner           varchar(30) ,
  mvname            varchar(30) ,
  capability_name   varchar(30) ,
  possible          character(1) ,
  related_text      varchar(2000) ,
  related_num       number ,
  msgno             integer ,
  msgtxt            varchar(2000) ,
  seq               number
) ;

exec dbms_mview.explain_mview('mv');
select capability_name, possible, msgtxt from MV_CAPABILITIES_TABLE
where  capability_name like 'REFRESH%';

CAPABILITY_NAME                  POSSIBLE    MSGTXT                                                                     
REFRESH_COMPLETE                 Y           <null>                                                                      
REFRESH_FAST                     Y           <null>                                                                      
REFRESH_FAST_AFTER_INSERT        Y           <null>                                                                      
REFRESH_FAST_AFTER_ONETAB_DML    Y           <null>                                                                      
REFRESH_FAST_AFTER_ANY_DML       Y           <null>                                                                      
REFRESH_FAST_PCT                 N           PCT is not possible on any of the detail tables in the materialized view    

If you're using a view in your materialized view, you need to create the MV logs on the tables the view uses.