Regarding Esri technical article #000011694: How To: Create Oracle Spatial function-based indexing
That article has out-of-date & incomplete information:
1. The article suggests that we should use the index type: MDSYS.SPATIAL_INDEX. That's the old way of doing it. Oracle now recommends we use the new version of that index type: MDSYS.SPATIAL_INDEX_V2.
5.1.1 Using System-Managed Spatial Indexes
Effective with Release 12.2, spatial indexes can be system-managed by specifying INDEXTYPE=MDSYS.SPATIAL_INDEX_V2 at index creation. You are strongly encouraged to use this index type for all new spatial indexes you create, regardless of whether the spatial table or the spatial index is partitioned, and you may also want to use it if you decide to re-create legacy spatial indexes.
2. Also, the Esri article says:
When creating a query layer that references an Oracle view containing an Oracle Spatial attribute, the attribute must have a spatial index. Without the spatial index, spatial queries cannot be executed.
That's no longer true.
Spatial Index No Longer Required
Creating and using a spatial index is no longer required for the use of any Oracle Spatial and Graph features. However, spatial indexes are highly recommended, and not using them can negatively affect performance in some cases.
Ideally, we would use spatial indexes whenever we can. But that's not always possible — and for small datasets, it's not always necessary.
Edit — 2022-06-16: In hindsight, I'm not sure if point #3 is correct. Needs verification?
3. There is information missing from the article about performance and USER_SDO_GEOM_METADATA.
In addition to creating an entry in USER_SDO_GEOM_METADATA for the SDO_GEOMETRY column/function, we also need to create an entry for any spatial views that use the column, so that those spatial views will be performant in the map in ArcGIS:
Answer from Albert Godfrind (Oracle Spatial Team):
If you want to also see the content of the view on a map (using some GIS tool), you will probably also need to define metadata for the view. This is NOT needed for spatial queries, but is a common requirement for GIS tools.
insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
'MY_SPATIAL_VV',
'SHAPE',
sdo_dim_array (
sdo_dim_element('long', -180.0, 180.0, 0.5),
sdo_dim_element('lat', -90.0, 90.0, 0.5)
),
4326
);
commit;
Could Esri please update this technical article so that it has up-to-date information?
Thanks.
