I want to create a fast refresh materialized view (18c) on a remote table. The MV would also have a GEOMETRY column.
Options for the GEOMETRY column datatype include:
- ESRI's proprietary implementation of ST_GEOMETRY (user-defined datatype; is an 'object' datatype)
- Oracle's SDO_GEOMETRY datatype
To start, I can successfully create a fast refresh MV without a GEOMETRY column:
create materialized view log on maximo.workorder with primary key; --remote table
grant select maximo.mlog$_workorder to schema_for_dblink; --I've given the dblink access to everything in this schema
create materialized view my_gis_schema.wo_mv
build immediate
refresh fast
start with sysdate next sysdate + (15/(60*60*24))
as
select
cast(workorderid as number(38,0)) as objectid,
wonum,
status,
--other fields
longitudex,
latitudey
from
maximo.workorder@my_dblink
The MV above works, but I want to store the XY coordinates from the remote table in a GEOMETRY column in the MV (right now, the coordinates are stored in number columns, not a geometry column).
Unfortunately, my options for the GEOMETRY column in an MV seem pretty limited:
-
Oracle doesn’t seem to support ESRI's ST_GEOMETRY datatype in MVs (more info here and here).
- The SQL would be:
sde.st_geometry(longitudex,latitudey,null,null, 26917 ) as shape
- The SQL would be:
-
Additionally, Oracle doesn't seem to support SDO_GEOMETRY in MVs with the fast refresh option on a remote table:
ORA-12015: cannot create a fast refresh materialized view from a complex query
- The SQL would be:
sdo_geometry(2001, 26917, sdo_point_type(longitudex,latitudey, null), null, null) as shape
- The SQL would be:
Question:
Is there a way to include a GEOMETRY column in a materialized view on a remote table, using the fast refresh option?
Best Answer
Regarding the SDO_GEOMETRY/fast refresh/remote table scenario:
The issue doesn't seem to happen when the MV selects from a local table. The issue only seems to happen when we select from a remote table/using a dblink. It seems to be normal Oracle behavior.
Using remote table/dblink (fails):
The steps were adapted from the answer in this post: Create fast-refresh MV over dblink on table without PK?
Using a local table (succeeds):
Of course, that doesn't answer your question about how to get a GEOMETRY column working.
But I think that test will at least be help demonstrate that the combination of 1) SDO_GEOMETRY, 2) fast refresh, and 3) using a remote table is what's causing the issue. If we remove any of those three things, then the MV seems to work fine.