0

I have 2 simple table which has columns like :

table1 :

  • "OBJECTID" NUMBER NOT NULL ENABLE
  • "OLCEK_ID" NUMBER(*,0)

table2 :

  • "OBJECTID" NUMBER NOT NULL ENABLE

i created materialized log view as below :

CREATE MATERIALIZED VIEW LOG ON table1 
  WITH ROWID(OBJECTID,olcek_id), SEQUENCE 
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON table2 
  WITH ROWID(OBJECTID), SEQUENCE 
  INCLUDING NEW VALUES;

And when creating materialized view i got an error : ORA-12015: cannot create a fast refresh materialized view from a complex query.

CREATE MATERIALIZED VIEW "PUBLIC"."TEST_MV"
  REFRESH FAST 
  AS SELECT a.OLCEK_ID 
  FROM TABLE1  a join TABLE2 ko ON a.OLCEK_ID= ko.OBJECTID;
 

I'm struggling to understand which part of my view is complex. Offical docs have example with joins.

1 Answer 1

2

5.2.2 About Materialized Views Containing Only Joins

If you specify REFRESH FAST, Oracle Database performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are:

  • 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.

create table table1(
"OBJECTID" NUMBER NOT NULL ENABLE,
"OLCEK_ID" NUMBER(*,0));

create table table2(
"OBJECTID" NUMBER NOT NULL ENABLE);

CREATE MATERIALIZED VIEW LOG ON table1 
  WITH ROWID(OBJECTID,olcek_id), SEQUENCE 
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON table2 
  WITH ROWID(OBJECTID), SEQUENCE 
  INCLUDING NEW VALUES;

Then:

CREATE MATERIALIZED VIEW "TEST_MV"
  REFRESH FAST as
SELECT a.OLCEK_ID, a.rowid as t1r, ko.rowid as t2r
FROM table1 a join table2 ko ON a.OLCEK_ID= ko.OBJECTID;

Materialized view created.

SQL>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.