Oracle-L: What makes a fast refresh materialized view too complex?

From: Mike Killough <mwkillough_at_hotmail.com>
Date: Wed, 17 Apr 2002 13:43:29 -0800
Message-ID: <F001.004473D9.20020417134329@fatcity.com>

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: mwkillough_at_hotmail.com

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: ListGuru_at_fatcity.com (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). Received on Wed Apr 17 2002 - 16:43:29 CDT