Esri docs need updating: Function-based spatial indexes

1093
4
05-14-2022 06:55 PM
Status: Implemented
Bud
by
Notable Contributor

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.

4 Comments
JonEmch

Thank you for the clarification Bud, I will work on updating this today.

JonEmch
Status changed to: In Product Plan

Working with internal sources on updating this document.

Bud
by

@JonEmch Thanks Jon! Looking forward to seeing the result.

JonEmch
Status changed to: Implemented