[oracle-l] Re: MVs
- From: Jared.Still@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 23 Jan 2004 09:13:22 -0800
Try MetaLink doc 101705.1
Ramón Estevez <REstevez@xxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
01/23/2004 06:56 AM
Please respond to oracle-l
To: <oracle-l@xxxxxxxxxxxxx>
cc:
Subject: [oracle-l] MVs
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@xxxxxxxxxx
809-535-8994
Other related posts:
