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