Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12015: cannot create a fast refresh materialized view .. (oracle, 10.2.0.1 , solaries spark)
ORA-12015: cannot create a fast refresh materialized view .. [message #436558] Thu, 24 December 2009 03:46 Go to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Dears

I am trying to create a Materialized view with "REFRESH FAST ON DEMAND"
but it generate error.
How can I solve it?


SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 24 16:35:57 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> CONN STLBAS/STLBAS@TEST107
Connected.
SQL> drop MATERIALIZED VIEW LOG ON stchrtac;

Materialized view log dropped.

SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON stchrtac
  2  WITH SEQUENCE, ROWID (headcode, acctname, dbcrclas)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL>
SQL> drop MATERIALIZED VIEW LOG ON sttrndtl;

Materialized view log dropped.

SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON sttrndtl
  2  WITH SEQUENCE, ROWID (compcode, docttype,acctcode,doctdate,jvlcamnt)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL>
SQL>
SQL> CREATE MATERIALIZED VIEW STLBAS.MV_STTRAILB
  2  LOGGING
  3  BUILD IMMEDIATE
  4  REFRESH FAST ON DEMAND
  5  WITH PRIMARY KEY
  6  AS
  7  SELECT   a.compcode,DECODE (a.docttype, 'JZ', a.docttype, 'AL') doctyp,
  8           b.headcode hedcod, a.acctcode glcode, a.doctdate,
  9         NVL (SUM (DECODE (a.dbcrcode, 'D', a.jvlcamnt)), 0) debit,
 10           NVL (SUM (DECODE (a.dbcrcode, 'C', a.jvlcamnt)), 0) credit,
 11           b.acctname glname, b.dbcrclas, SYSDATE - 1 timstamp,SYSDATE jobstamp
 12      FROM sttrndtl a, stchrtac b
 13     WHERE a.doctdate <= SYSDATE - 1
 14     AND   a.acctcode = b.acctcode
 15  GROUP BY a.compcode,
 16           DECODE (a.docttype, 'JZ', a.docttype, 'AL'),
 17           b.headcode,
 18           a.acctcode,
 19           a.doctdate,
 20           b.acctname,
 21           b.dbcrclas;
    FROM sttrndtl a, stchrtac b
         *
ERROR at line 12:
ORA-12015: cannot create a fast refresh materialized view from a complex query

SQL>



Regards
Halim
Re: ORA-12015: cannot create a fast refresh materialized view .. [message #436561 is a reply to message #436558] Thu, 24 December 2009 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to add a "count(*)" column in your select statement, even if you don't need it but the usage of condition like "a.doctdate <= SYSDATE - 1" may prevent from fast refreshing.

Regards
Michel
Re: ORA-12015: cannot create a fast refresh materialized view .. [message #436563 is a reply to message #436558] Thu, 24 December 2009 03:56 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Post in forum itself.

Cause and Action.

regards,
Delna
Re: ORA-12015: cannot create a fast refresh materialized view .. [message #436566 is a reply to message #436558] Thu, 24 December 2009 04:13 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member
Hi,

Try to remove the group by clause.
For more info visit:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#i1006803

Regards
Soni
Re: ORA-12015: cannot create a fast refresh materialized view .. [message #436567 is a reply to message #436561] Thu, 24 December 2009 04:14 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Thanks Michel

It also giving error .

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 24 17:05:58 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn stlbas/stlbas@test107
Connected.
SQL>
SQL> CREATE MATERIALIZED VIEW STLBAS.MV_STTRAILB
  2  LOGGING
  3  BUILD IMMEDIATE
  4  REFRESH FAST ON DEMAND
  5  WITH PRIMARY KEY
  6  AS SELECT   a.compcode,DECODE (a.docttype, 'JZ', a.docttype, 'AL') doctyp,
  7           b.headcode hedcod, a.acctcode glcode, a.doctdate,
  8         NVL (SUM (DECODE (a.dbcrcode, 'D', a.jvlcamnt)), 0) debit,
  9           NVL (SUM (DECODE (a.dbcrcode, 'C', a.jvlcamnt)), 0) credit,
 10           b.acctname glname, b.dbcrclas, SYSDATE - 1 timstamp,SYSDATE jobstamp,count(*) num
 11      FROM sttrndtl a, stchrtac b
 12     WHERE a.doctdate <= SYSDATE - 1
 13     AND   a.acctcode = b.acctcode
 14  GROUP BY a.compcode,
 15           DECODE (a.docttype, 'JZ', a.docttype, 'AL'),
 16           b.headcode,
 17           a.acctcode,
 18           a.doctdate,
 19           b.acctname,
 20           b.dbcrclas;
    FROM sttrndtl a, stchrtac b
         *
ERROR at line 11:
ORA-12015: cannot create a fast refresh materialized view from a complex query


SQL> ed
Wrote file afiedt.buf

  1  CREATE MATERIALIZED VIEW STLBAS.MV_STTRAILB
  2  LOGGING
  3  BUILD IMMEDIATE
  4  REFRESH FAST ON DEMAND
  5  WITH PRIMARY KEY
  6  AS SELECT   a.compcode,DECODE (a.docttype, 'JZ', a.docttype, 'AL') doctyp,
  7           b.headcode hedcod, a.acctcode glcode, a.doctdate,
  8         NVL (SUM (DECODE (a.dbcrcode, 'D', a.jvlcamnt)), 0) debit,
  9           NVL (SUM (DECODE (a.dbcrcode, 'C', a.jvlcamnt)), 0) credit,
 10           b.acctname glname, b.dbcrclas ---, SYSDATE - 1 timstamp,SYSDATE jobstamp,count(*) num
 11      FROM sttrndtl a, stchrtac b
 12     WHERE ---a.doctdate <= SYSDATE - 1
 13        a.acctcode = b.acctcode
 14  GROUP BY a.compcode,
 15           DECODE (a.docttype, 'JZ', a.docttype, 'AL'),
 16           b.headcode,
 17           a.acctcode,
 18           a.doctdate,
 19           b.acctname,
 20*          b.dbcrclas
SQL> /
    FROM sttrndtl a, stchrtac b
         *
ERROR at line 11:
ORA-12015: cannot create a fast refresh materialized view from a complex query


SQL>



Re: ORA-12015: cannot create a fast refresh materialized view .. [message #436568 is a reply to message #436566] Thu, 24 December 2009 04:15 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
GROUP BY should not be a problem.
Re: ORA-12015: cannot create a fast refresh materialized view .. [message #436580 is a reply to message #436566] Thu, 24 December 2009 05:13 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

may be group by is not a problem


SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 24 18:06:08 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn scott/tiger@beftn
Connected.
SQL> drop MATERIALIZED VIEW LOG ON scott.emp;
drop MATERIALIZED VIEW LOG ON scott.emp
*
ERROR at line 1:
ORA-12002: there is no materialized view log on table "SCOTT"."EMP"


SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON scott.emp
  2  WITH SEQUENCE, ROWID (JOB, DEPTNO, SAL)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL>
SQL>
SQL> drop MATERIALIZED VIEW LOG ON scott.dept
  2
SQL>  CREATE MATERIALIZED VIEW LOG ON scott.dept
  2  WITH SEQUENCE, ROWID (DEPTNO)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL>
SQL>  DROP MATERIALIZED VIEW scott.sal_dept_mv;
 DROP MATERIALIZED VIEW scott.sal_dept_mv
*
ERROR at line 1:
ORA-12003: materialized view "SCOTT"."SAL_DEPT_MV" does not exist

SQL>
SQL>  CREATE MATERIALIZED VIEW scott.sal_dept_mv
  2             NOLOGGING
  3             PARALLEL
  4             BUILD IMMEDIATE
  5             REFRESH fast ON DEMAND
  6           ENABLE QUERY REWRITE
  7             AS
  8            SELECT e.job, e.deptno, sum(e.sal)
  9            FROM emp e, dept d
 10            WHERE e.deptno=d.deptno
 11            GROUP BY e.job, e.deptno;

Materialized view created.

SQL>



Regards
Halim
Re: ORA-12015: cannot create a fast refresh materialized view .. [message #436586 is a reply to message #436580] Thu, 24 December 2009 05:37 Go to previous message
soni_7
Messages: 33
Registered: July 2005
Member

Good you got it. It was just one of the possibilities i thought of before.
Previous Topic: update query required
Next Topic: A table may be outer join?
Goto Forum:
  


Current Time: Thu Sep 29 20:58:56 CDT 2016

Total time taken to generate the page: 0.17708 seconds