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

Question

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:

1. 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

2. 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

---

Question:

Is there a way to include a GEOMETRY column in a materialized view on a remote table, using the fast refresh option?

Answer 1

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?

SQL> create table t5 (c1 number not null unique, c2 number);

Table created.
SQL> create materialized view log on t5 with primary key;

Materialized view log created.

SQL> create materialized view mv5 refresh fast on demand as
  2  select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape
  3  from t5@self;
select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape

             *
ERROR at line 2:
ORA-12015: cannot create a fast refresh materialized view from a complex query

---

Using a local table (succeeds):

SQL> create table t5 (c1 number not null unique, c2 number);

Table created.

SQL> create materialized view log on t5 with primary key;

Materialized view log created.

SQL> create materialized view mv5 refresh fast on demand as
  2  select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape
  3  from t5;

Materialized view created.

SQL> insert into t5 values (1, 1);

1 row created.

SQL> insert into t5 values (2, 2);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('MV5', 'f');

PL/SQL procedure successfully completed.

SQL> select refresh_method from user_mviews where mview_name = 'MV5';

REFRESH_METHOD
--------
FAST

---

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.

Answer 2

A colleague has suggested that it's possible to solve this issue by:

"...creating a public synonym in the target DB on the top of DB link; the remote table will act like local table."

Supposedly, the synonym solves the issue of creating a materialized view with FAST refresh on a remote table with an SDO_GEOMETRY column.

CREATE public SYNONYM maximo.workorder FOR maximo.workorder@my_dblink;

-- ... then create the materialized view. 

My colleague says it has been tested and it works; it was tested using a fake primary key, which is consistent with my setup.

Unfortunately, I haven't had a chance to test it myself. I don't have CREATE MATERIALIZED VIEW privileges at the moment, so I haven't been able to verify if it works or not.