Materialized view with FAST refresh on remote table: How to include a SHAPE column?

710
0
12-11-2020 01:24 PM
Bud
by
Regular Contributor II
 

I want to create an Oracle 18c materialized view with the fast refresh option on a remote table (in an enterprise GDB).

I can do this successfully without a SHAPE column:

create materialized view log on source_system.workorder with primary key;
grant select source_system.mlog$_workorder to schema_for_dblink;

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, --workorderid is a fake primary key; it has a NOT NULL constraint and a unique index
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    source_system.workorder@my_dblink

Problem:

I want to store the XY coordinates from the source table in a SHAPE column in the MV. Unfortunately, my options seem pretty limited:

  1. Oracle doesn’t seem to support SDE.ST_GEOMETRY in MVs (more info here and here).

    • The SQL would be: 
      sde.st_geometry(longitudex,latitudey,null,null, 26917 ) as shape

  2. Additionally, Oracle doesn't seem to support SDO_GEOMETRY in MVs on a remote table with the fast refresh option: 
    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

Question:

Is there a way to include a SHAPE column in a materialized view on a remote table with the fast refresh option?

0 Kudos
0 Replies