0

EGDB: Oracle 18c; ArcGIS 10.7.1

External system: Oracle 12c


I have a materialized view in an EGDB that syncs data from an external system (on a schedule via a dblink).

The materialized view has an SDO_GEOM column (since using SDE.ST_GEOMETRY isn't possible due to an Oracle limitation here and here).

create materialized view my_gis_schema.wo_mv --EGDB
build immediate 
refresh force 
on demand 
start with sysdate next sysdate + (5/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey,
    sdo_geometry(2001, 80085, sdo_point_type(longitudex, latitudey, null), null, null) as shape
from
    maximo.workorder@gistomax --dblink

create materialized view log on maximo.workorder with rowid;  --external system

Question:

When I add the MV to ArcMap, the layer works fine.

However, the performance is surprisingly poor. It takes 10 seconds to load 6,000 points in the map.

In contrast, if I ignore the static SDO_GEOM column, and instead construct a shape column on-the-fly from the non-spatial XY columns, the performance is much better (1-second):

  • I generated an SDO_GEOM column on-the-fly in a regular view (fast!)
  • And alternatively, I created an XY event layer from the non-spatial XY columns in the MV (also fast)

Why is the static SDO_GEOM column so much slower than columns that are calculated on-the-fly?

This seems very counterintuitive to me.

4
  • Perhaps you have indexes on longitudex, latitudey but not on SDO_GEOM. – user30184 Nov 24 '20 at 21:16
  • You should create the geometry with the appropriate SRID for GCS WGS84, and make sure the spatial index is constructed. – Vince Nov 25 '20 at 1:13
  • @Vince I wonder if I can use the Add Spatial Index geoprocessing tool for this (instead of doing it manually). – User1973 Nov 25 '20 at 3:46
  • If I already have the SQL window open, I'm unlikely to start another app, but sure. – Vince Nov 25 '20 at 4:53
2

You may not have indexed the constructed geometry.

SDE_Geometry(2001, 80085, SDO_Point_Type(longitudex, latitudey, NULL), NULL, NULL) as shape

Create entry in USER_SDO_GEOM_METADATA.

Then create spatial index:

CREATE INDEX WO_MV_SX ON WO_MV(shape) INDEXTYPE IS mdsys.spatial_index PARAMETERS ('layer_gtype=POINT');

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.