I am not clear on what will make a fast refresh materialized view too
complex. What functions cannot be used in a FRMV that is a single table
aggregation? The following example won't work if I try to add the decode:
create materialized view log on emp
tablespace &&tbsname
with sequence, primary key, rowid (job, hiredate, sal, comm)
including new values
;
create materialized view emp_mv2
tablespace &&tbsname
build immediate
refresh fast on commit
with rowid
enable query rewrite
as
select count(*) n, job, to_char(hiredate,'DD-MON-YYYY') hiredate,
sum(sal) sum_sal, count(sal) cnt_sal, sum(comm) sum_comm,
count(comm) cnt_comm
-- , sum(decode(status,'O',sal,comm)),
count(decode(status,'O',sal,comm)) -- Add this line and it is not fast
refresh.
from emp
group by job, to_char(hiredate,'DD-MON-YYYY')
/
Thanks,
Mike
_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mike Killough
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
