[oracle-l] Re: MVs

Your error says the following:

ORA-12015: cannot create a fast refresh materialized view from a complex query

That means that you are trying to create a MV using a join and not just a subset of a single table. Use "REFRESH COMPLETE" instead of "REFRESH FAST" and you'll be OK.

On 01/23/2004 09:56:59 AM, Ramón Estevez wrote:
> 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 - 09:31:46 CST