oraclematerialized-viewsoracle21c

Cannot create materialized view with "on statement" clause

While creating materialized view with on statement I am getting below error.

ORA-32428: on-statement materialized join view error: Shape of MV is not supported 32428. 0000 - "on-statement materialized join view error: %s" *Cause: An error related to on-statement materialized join view was received. *Action: Refer to the error message for more information.

I am using oracle version 21c

Here is what i did

CREATE TABLE "TBL1" (
 "COL1" VARCHAR2(20 BYTE),
 "COL2" VARCHAR2(20 BYTE),
 "COL3" VARCHAR2(20 BYTE),
 "COL4" NUMBER
;

create MATERIALIZED view log on tbl1 with rowid;

create materialized view mv3
build DEFERRED
refresh fast with rowid
on statement
enable query rewrite
as
select rowid rid, COL1,COL2 from tbl1;
Solution

  • From the docs

    "The base tables referenced in the materialized view’s defining query must be connected in a join graph that uses the star schema or snowflake schema model. The query must contain exactly one centralized fact table and one or more dimension tables, with all pairs of joined tables being related using primary key-foreign key constraints."

    eg

    SQL> create table par ( p int primary key, d date);

Table created.

SQL> create table chd ( c int primary key, p int references par (p), d1 date);

Table created.

SQL>
SQL> create materialized view log on par with rowid;

Materialized view log created.

SQL> create materialized view log on chd with rowid;

Materialized view log created.

SQL>
SQL> create materialized view mv3
  2  refresh fast with rowid
  3  on statement
  4  enable query rewrite
  5  as
  6  select c.rowid rid, p.p, p.d, c.c, c.d1
  7  from par p, chd c
  8  where p.p = c.p;

Materialized view created.