0

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

2 Answers 2

0

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.

1
  • Thank you. It was a bucket of cold water! I will consider migrating to regular views after pre-processing all geoprocessing in another lookup table to keep things fast as static materialized views.
    – salgado
    Feb 26, 2023 at 15:46
0

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.

2
  • Oh, thank you! I didn't know that Oracle's geometries are objects.
    – salgado
    Feb 24, 2023 at 16:54
  • I tried removing the left join from the query but I got another error. I will leave it as an edit.
    – salgado
    Feb 24, 2023 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.