Does Select By Location (ArcObjects) use spatial indexes?

[Bud]

Does the Select By Location tool in ArcGIS Pro make use of spatial indexes in the DB when performing spatial operations? For example, SDO_GEOMETRY (Oracle) and SDE._ST_GEOMETRY spatial indexes.

I want to understand why Select By Location/ArcObjects is so fast compared to spatial SQL queries. I talk more about it here:

1. Why are spatial SQL queries so much slower than Select By Location?
2. Idea: “Select By Location” Feature Layer — Spatial query that is fast, modifiable, and refreshable

[DuncanHornby]

I'm going to stick my neck out and say yes. I base that opinion on my experience. If the input dataset to a select by location is a shapefile without it's spatial index I often observe poor performance. Add a spatial index and I see significant improvement with such tools.  I've never used SDE so I'm assuming that would behave the same as I see that as a superior storage format.

[Bud]

Thanks. Interesting.

I did a test on some SDO_GEOMETRY (Oracle) test data.

• With spatial indexes:
  • Select By Location - Intersect: 0.5 seconds
  • Spatial SQL query: 4.5 seconds
• Without spatial indexes (dropped via SQL client):
  • Select By Location - Intersect: 6 seconds
  • Spatial SQL query: 1hr 50mins.

Conclusion:

With SDO_GEOMETRY data, Select By Location seems to use spatial indexes. But it's still relatively fast when spatial indexes are missing; way faster than a spatial SQL query.

[Bud]

Side note:

In older versions of Oracle, SDO_GEOMETRY spatial indexes were required in order for spatial querying to work. The Esri docs used to say this: (until they were updated in 2022)

How To: Create Oracle Spatial function-based indexing

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.

However, in Oracle 18c and beyond, spatial indexes are optional but strongly recommended. 

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.