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))
    cast(workorderid as number(38,0)) as objectid,
    --other fields
    sdo_geometry(2001, 80085, sdo_point_type(longitudex, latitudey, null), null, null) as shape
    maximo.workorder@gistomax --dblink

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


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.

  • Perhaps you have indexes on longitudex, latitudey but not on SDO_GEOM.
    – user30184
    Nov 24, 2020 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, 2020 at 1:13
  • @Vince I wonder if I can use the Add Spatial Index geoprocessing tool for this (instead of doing it manually).
    – User1974
    Nov 25, 2020 at 3:46
  • If I already have the SQL window open, I'm unlikely to start another app, but sure.
    – Vince
    Nov 25, 2020 at 4:53

1 Answer 1


You may not have indexed the constructed geometry.

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


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 and acknowledge you have read our privacy policy.

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