Thursday, May 29, 2014

FAST Refresh of Materialized View Returns ORA-12004

I tried to do a FAST refresh of a materialized view in Oracle 11.2 but it failed with an ORA-12004:
 
SQL> begin
  2  dbms_mview.refresh('ebase.m_gridpoint2',method=>'F');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view
"EBASE"."M_GRIDPOINT2"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 2
 
SQL>
 
I checked the documentation provided by the software supplier. It said I could not do this as the materialized view in question contained a NOT EXISTS clause. I looked at the source of the materialized view and saw that it did:
 
SQL> l
  1  select query
  2  from dba_mviews
  3  where owner = 'EBASE'
  4* and mview_name = 'M_GRIDPOINT2'
SQL> /
 
QUERY
-------------------------------------------------------
SELECT GPT.NR GRIDPOINTNR,
       DECODE (GPT.PRODUCTTYPE,  0, 'E',  1, 'G',  'U')
PRODUCTTYPE,
       GPT.EANCODE EANCODE,
       NAR.EANCODE NETAREAEAN,
       MROL.EANCODE GRIDOPERATOREAN,
       GPT.ROWID GPTROWID,
       NAR.ROWID NARROWID,
       MROL.ROWID MROLROWID
  FROM D_GRIDPOINT GPT,
       D_NETAREA NAR,
       D_MARKETROLE MROL
WHERE GPT.GRIDOPERNR = MROL.NR(+)
AND   GPT.NETAREANR = NAR.NR(+)
AND NOT EXISTS (SELECT 1
                    FROM   D_GRIDPOINTCONNECTION GPC
                    WHERE  GPT.NR = GPC.GRIDPOINTNR)
 
SQL>
 
… so I tried a COMPLETE refresh and it worked:
 
SQL> begin
  2  dbms_mview.refresh('ebase.m_gridpoint2',method=>'C');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL>

No comments: