Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> [oracle-l] MVs

[oracle-l] MVs

From: Ramón Estevez <REstevez_at_blh.com.do>
Date: Fri, 23 Jan 2004 10:56:59 -0400
Message-ID: <80205D3A07BB114888ECFA425824BACE2AEA55@bdiblh02.bdiblh.apps>


Hi list,
I want to create a MV between those two tables and I get an error, here is the case:

I checked metalink and the documentation either is not clear or I don't understand, choose that one, but just are two tables with a single join.

TIA SQL> DESC ORIGINALES

 Name                            Null?    Type

------------------------------- -------- ----
GRUPO NOT NULL NUMBER(2) COMPANIA NOT NULL NUMBER(2) NUMERO_ORIGINAL NOT NULL VARCHAR2(20) NRO_DESCRIPCION NUMBER(4) LINEA NUMBER(2) FECHA_CREACION NOT NULL DATE PRECIO_VENTA NUMBER(8,2) ESTATUS_PEDIR VARCHAR2(1) SQL> DESC DESCRIPCIONES Name Null? Type
------------------------------- -------- ----
NRO_DESCRIPCION NOT NULL NUMBER(4) DESCRIPCION NOT NULL VARCHAR2(30)

SQL> CREATE MATERIALIZED VIEW LOG ON ORIGINALES   2 WITH SEQUENCE, ROWID
  3 (GRUPO, COMPANIA, NUMERO_ORIGINAL, NRO_DESCRIPCION, LINEA, PRECIO_VENTA)   4 INCLUDING NEW VALUES; Snapshot log created.

SQL> CREATE MATERIALIZED VIEW LOG ON DESCRIPCIONES   2 WITH SEQUENCE, PRIMARY KEY
  3 (DESCRIPCION)
  4 INCLUDING NEW VALUES; Snapshot log created.

SQL> CREATE MATERIALIZED VIEW MV_ORIGINALES   2 BUILD IMMEDIATE
  3 REFRESH FAST ON COMMIT
  4 ENABLE QUERY REWRITE
  5 AS

  6     SELECT  O.GRUPO,       O.COMPANIA,      O.NUMERO_ORIGINAL,
  7             D.DESCRIPCION, O.LINEA,         O.PRECIO_VENTA
  8        FROM ORIGINALES O, DESCRIPCIONES D
  9     WHERE   O.NRO_DESCRIPCION = D.NRO_DESCRIPCION
 10  /
      FROM ORIGINALES O, DESCRIPCIONES D
           *

ERROR at line 8:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

SQL> ED
Wrote file afiedt.buf

  1 CREATE MATERIALIZED VIEW MV_ORIGINALES

  2      BUILD IMMEDIATE
  3      REFRESH FAST ON DEMAND
  4      ENABLE QUERY REWRITE
  5      AS
  6         SELECT  O.GRUPO,       O.COMPANIA,      O.NUMERO_ORIGINAL,
  7                 D.DESCRIPCION, O.LINEA,         O.PRECIO_VENTA
  8            FROM ORIGINALES O, DESCRIPCIONES D
  9*        WHERE   O.NRO_DESCRIPCION = D.NRO_DESCRIPCION
SQL> /
          FROM ORIGINALES O, DESCRIPCIONES D
               *

ERROR at line 8:
ORA-12015: cannot create a fast refresh materialized view from a complex query

Ramon E. Estevez
restevez_at_blh.com.do
809-535-8994 Received on Fri Jan 23 2004 - 08:56:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US