Background:
A predicate such as FUNC(VAL)IS NOT NULL can utilize a regular non-spatial index (i.e. B-tree).
A predicate such as SDO_ANYINTERACT(geometry1, geometry2)can utilize a spatial index.
A spatial index can't be used as the exclusive data source for a query. The query will still do a full table scan to satisfy the SELECT list, even if the only column in the SELECT list is the indexed geometry column.
Whereas a non-spatial index (B-tree) can be used as the exclusive data source for a query as long as all of the columns in the SELECT list are included in the index. That avoids the need for a full table scan. Note: "Abstract datatype" columns like SDO_GEOMETRY can't be included in a non-spatial index (B-tree): Idea - Support function-based indexes on abstract datatypes..
Idea:
Enhance spatial indexes so that they can be invoked by an FUNC(VAL) IS NOT NULL predicate in the WHERE clause — so that the data in the SELECT list is exclusively selected from the index (when we’re only selecting the geometry column). That would essentially let us precompute the calculated geometry column in the spatial index instead of computing it at run-time.
I know using a spatial index to simply precompute a column isn’t what Oracle had in mind when designing spatial indexes. But with non-spatial indexes, using the index to precompute a column is a legitimate use pattern. Why not let us do the same thing for geometry columns?
Reason: Creating indexes is easy and practical in standard GIS environments. As data creators, we have the CREATE INDEX privilege. Whereas the alternatives are much more difficult, since they require custom mechanisms to be set up by administrators (set up a materialized view or build a trigger/scheduled job to populate a separate table).
Related: Why can't a spatial index be invoked by the SELECT list, yet a non-spatial index can? (https://community.oracle.com/tech/apps-infra/discussion/comment/16840987/#Comment_16840987)