Sample Header Ad - 728x90

Unable to Create Fast Refresh Materialized View with Geometry Field

0 votes
2 answers
513 views
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
Asked by salgado (3 rep)
Feb 24, 2023, 04:06 PM
Last activity: Feb 26, 2023, 12:16 PM