Unable to Create Fast Refresh Materialized View with Geometry Field

Question

I am receiving the following error while trying to create a materialized view with a geometry field on Oracle (21c):

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

I will leave here some snippets to reproduce this error.

First of all, we will need to create two simple tables and its logs:

CREATE TABLE signos.lookup_table (
    objectid NUMBER (38) NOT NULL,
    table_rowid rowid NOT NULL
);


CREATE TABLE signos.dirty_table (
    objectid NUMBER (38) NOT NULL,
    shape MDSYS.GEOMETRY
);

CREATE MATERIALIZED VIEW LOG ON signos.lookup_table WITH ROWID INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON signos.dirty_table WITH ROWID INCLUDING NEW VALUES;

This should work since we omitted the geometry field from the materialized view query:

CREATE MATERIALIZED VIEW signos.mv_filtered REFRESH FAST ON
DEMAND WITH ROWID AS
SELECT
    dt.objectid,
    dt.rowid AS dt_rowid,
    lt.rowid AS lt_rowid
FROM
    signos.dirty_table dt,
    signos.lookup_table lt
WHERE
    lt.table_rowid (+) = dt.rowid
    AND lt.table_rowid IS NULL;

Unfortunately, we can't say the same about this one:

CREATE MATERIALIZED VIEW signos.mv_filtered REFRESH FAST ON
DEMAND WITH ROWID AS
SELECT
    dt.objectid,
    dt.shape, -- !!!
    dt.rowid AS dt_rowid,
    lt.rowid AS lt_rowid
FROM
    signos.dirty_table dt,
    signos.lookup_table lt
WHERE
    lt.table_rowid (+) = dt.rowid
    AND lt.table_rowid IS NULL;

Which returns the aforementioned error:

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

I am not sure if this error is expected, there isn't much documentation on this topic. The production version of these materialized views will have up to 8 million records so fast-refresh will come in handy to my use case. That said, any help will be very appreciated.

Edit 1

Thanks to @mustaccio for pointing out Oracle's documentation on the topic. I removed the join as recommended:

CREATE MATERIALIZED VIEW signos.mv_filtered REFRESH FAST ON
DEMAND WITH ROWID AS
SELECT
    dt.rowid,
    dt.objectid,
    dt.shape
FROM
    signos.dirty_table dt;

And I got a different error:

ORA-30373: object data types are not supported in this context

TIA

Answer 1

You can find the answer here:

Getting ORA-30373 When Creating A Materialized View Containing ST_Geometry (Doc ID 1067173.1)

MDSYS.GEOMETRY (ST_GEOMETRY) object type is not defined as FINAL.

ST_GEOMETRY can not be defined as FINAL because how it was designed.

But object types must be defined as FINAL to be referenced in materialized views.

Answer 2

The manual says this:

You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.