0

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?

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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