materialized view with primary key not work

Question

Goodnight, I am having trouble creating a materialized view type primary key, the 2 tables have primary key. as follows: the table "cotizacion" has two primary keys, -fecha -neumatico

the table "table_hija1", has a primary key: "id"

what may be the problem, I will appreciate your help. Thanks,

obs:

-It has to be of the refresh on demand type

-table_child1, is a large table that contains data from 50 small tables. It is not possible to divide

them into 50 small tables.

CREATE MATERIALIZED VIEW LOG ON Cotizacion
WITH primary key
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON tabla_hija1
WITH  primary key
INCLUDING NEW VALUES;

create materialized view vm_prueba3
refresh fast on demand 
with primary key
as
select 
c.id id1,
e.id id2,
f.id id3,o.neumatico,
o.idproceso,
o.fecha,
o.precio

from Cotizacion o, tabla_hija1 c, tabla_hija1 e,tabla_hija1 f
where
   ( o.estado=c.vvalor(+) and c.tipo_filtro=1 )
   and
   ( o.segmento=e.vvalor(+) and e.tipo_filtro=2)

error:

ORA-12052: no se puede realizar un refrescamiento rápido de la vista materializada SYSTEM.VM_PRUEBA3

00000 - "cannot fast refresh materialized view %s.%s"

*Cause: Either ROWIDs of certain tables were missing in the definition or

the inner table of an outer join did not have UNIQUE constraints on

join columns.

*Action: Specify the FORCE or COMPLETE option. If this error is got

during creation, the materialized view definition may have be

changed. Refer to the documentation on materialized views.

pmdba pmdba · Accepted Answer · 2021-02-27T13:57:26

"ROWIDs of certain tables were missing in the definition"

From the documentation, for MVs with joins performing FAST REFRESH ON DEMAND:

A materialized view log must be present for each detail table unless the table supports partition change tracking (PCT). Also, when a materialized view log is required, the ROWID column must be present in each materialized view log.
The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition.

https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html#GUID-3B903558-0C98-4033-9BCD-4A146220E868

You need to include the rowids of each source table in your materialized view:

CREATE MATERIALIZED VIEW LOG ON Cotizacion
WITH rowid
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON tabla_hija1
WITH rowid
INCLUDING NEW VALUES;

create materialized view vm_prueba3
refresh fast on demand 
with rowid
as
select 
c.rowid c_rowid,
e.rowid e_rowid,
f.rowid f_rowid,
o.rowid o_rowid,
c.id id1,
e.id id2,
f.id id3,o.neumatico,
o.idproceso,
o.fecha,
o.precio

from Cotizacion o, tabla_hija1 c, tabla_hija1 e,tabla_hija1 f
where
   ( o.estado=c.vvalor(+) and c.tipo_filtro=1 )
   and
   ( o.segmento=e.vvalor(+) and e.tipo_filtro=2)

materialized view with primary key not work

1 Answers