SDO_GEOM materialized view: Why is static field slower than dynamic field?

Question

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.

Answer 1

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');