error creating materialized view in oracle - ORA-12052

Question

good night i am trying to create a materialized view in oracle and i have the following error message. I know there are several restrictions but I can't identify what the error is. please appreciate your help.

obs: -the update method must be incremental.

none of the tables have primary keys and I can't create them because I don't have access.

CREATE MATERIALIZED VIEW LOG ON ordenes

WITH ROWID

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON tabla_hija

WITH ROWID

INCLUDING NEW VALUES;

create materialized view vm_prueba2

refresh fast on demand

with rowid

as

select ordenid,o.empleadoid,o.clienteid,fechaorden,descuento,nvl(c.desccripcion,'')as ddesc,e.desccripcion

from ordenes o,tabla_hija c,tabla_hija e

where

( o.clienteid=c.valor(+) and c.id_tabla=1 or c.valor is null ) and

( o.empleadoid=e.valor(+) and e.id_tabla=2 or e.valor is null )order by ordenid asc;

Informe de error - ORA-12052: 12052. 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-17T03:37:58

"ROWIDs of certain tables were missing in the definition"

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

create materialized view vm_prueba2
refresh fast on demand
with rowid
as
select 
   o.rowid o_rowid,
   c.rowid c_rowid,
   e.rowid e_rowid,
   ordenid,
   o.empleadoid,
   o.clienteid,
   fechaorden,
   descuento,
   nvl(c.desccripcion,'') as ddesc,
   e.desccripcion
from ordenes o, tabla_hija c, tabla_hija e
where
   ( o.clienteid=c.valor(+) and c.id_tabla=1 or c.valor is null ) and
   ( o.empleadoid=e.valor(+) and e.id_tabla=2 or e.valor is null );

I would also recommend not sorting your MV content, as it will add unnecessary overhead to the generation. The order of the rows stored on disk doesn't matter, and it will generate a lot of extra disk I/O while it performs the sort in the Temp tablespace.

The sort will most likely be dropped by Oracle for the refreshes anyway, as Oracle creates its own SQL automatically and only uses yours for the initial MV creation. Save your sorts for client/user queries.

error creating materialized view in oracle - ORA-12052

1 Answers