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