ORA-12015: cannot create a fast refresh materialized view from a complex query with 2 joins

CREATE MATERIALIZED VIEW anon_flag_mv
    REFRESH FAST ON DEMAND
AS
SELECT t2.id               AS t2_id,
       t3.cfg_id           AS cfg_id,
       t3.parent_ref_id    AS parent_ref_id,
       t1.category_ref_id  AS category_ref_id,
       t1.is_on_demand     AS is_on_demand,
       t3.id               AS t3_id,
       t1.id               AS t1_id
FROM table_one t1
         JOIN table_two t2
              ON t2.id = t1.category_ref_id
         JOIN table_three t3
              ON t1.id = t3.parent_ref_id;

I'm trying to create a materialized view in Oracle with FAST REFRESH, but I get the following error:

ORA-12015: cannot create a fast refresh materialized view from a complex query

I've already created logs

CREATE MATERIALIZED VIEW LOG ON table_one
    WITH PRIMARY KEY, SEQUENCE
    (category_ref_id, is_on_demand)
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON table_two
    WITH PRIMARY KEY, SEQUENCE
    (cfg_id, parent_ref_id)
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON table_three
    WITH PRIMARY KEY, SEQUENCE
    INCLUDING NEW VALUES;

How can I create materialized view and use fast refresh?

Solution

The support of JOIN in MATERIALIZED VIEW with REFRESH FAST is limited in most recent versions and inexistent in olders. Try using the old form FROM ...t1, ...t2, ...t3 WHERE the join conditions instead. You also better analyze the MV with dbms_mview.explain_mview, it will give you what is supported or not based on its definition. You need the mv_capabilities_table in your schema:

create table mv_capabilities_table (
   statement_id      varchar(30),
   mvowner           varchar(30),
   mvname            varchar(30),
   capability_name   varchar(30),
   possible          character(1),
   related_text      varchar(2000),
   related_num       number,
   msgno             integer,
   msgtxt            varchar(2000),
   seq               number
);

that will store the results of execute dbms_mview.explain_mview('ANON_FLAG_MV') ;

In your case you also have to add the ROWID in the 3 VIEW LOG and the 3 rowids in the MV definition.