Equivalent to USER_SDO_GEOM_METADATA for SDE.ST_GEOMETRY

Question

Background - SDO_GEOMETRY:

If I were to create a function-based spatial index that returns an SDO_GEOMETRY, I would manually insert records into SDO_GEOMETRY's USER_SDO_GEOM_METADATA for these purposes:

1. To register the indexed function, similar to what's done for SDO_GEOMETRY columns in tables.
2. Register a view that uses the indexed geometry column — so that GIS software knows that the index should be used for the view (to boost performance when displaying the view/features in the map).

• An answer from @AlbertGodfrind on function-based spatial indexes
• Oracle docs: 10.2 SDO_GEOMETRY Objects in Function-Based Indexes

---

Question - SDE.ST_GEOMETRY:

I want to create a function-based spatial index on an SDE.ST_GEOMETRY function (not an SDO_GEOMETRY function).

In a quick test, I was able to successfully create a function-based spatial index that returns an ST_GEOMETRY:

CREATE INDEX atn_startpoint_shape ON infrastr.active_transportation 
    (sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))),300125)) 
         INDEXTYPE IS SDE.ST_SPATIAL_INDEX parameters ('ST_GRIDS=320 ST_SRID=300125');
--                             ↑↑↑


Index ATN_STARTPOINT_SHAPE created.

But it seems to me that a piece is still missing: the equivalent of registering the index (and a view) with a table like USER_SDO_GEOM_METADATA.

---

Is there an equivalent to USER_SDO_GEOM_METADATA for SDE.ST_GEOMETRY?

Related idea: ST_Geometry: Function-based spatial indexes

Answer 1

Index SQL Hint:

I don't think an index hint is an equivalent mechanism, but maybe it's what's done for ST_GEOMETRY instead, since we don't use USER_SDO_GEOM_METADATA for ST_GEOMETRY:

An Oracle hint provides directive to the optimizer in choosing an execution plan for the SQL statement being executed.

The Oracle INDEX hint instructs the optimizer to use an index scan for the specified table. Use the INDEX hint for function-based, domain, B-tree, bitmap, and bitmap join indexes.

When working with tables containing ST_Geometry attributes and a st_spatial_index, specify the Oracle INDEX hint and the name of the st_spatial_index to instruct the optimizer to access the data by way of the index.

As stated in Oracle's SQL Reference documentation, ('If the INDEX hint specifies a single available index, then the database performs a scan on this index. The optimizer does not consider a full table scan or a scan of another index on the table.') when an INDEX hint is specified the optimizer uses the index as the primary access path.

Esri Technical Article: How To: Set a SQL hint to instruct the Oracle optimizer in choosing an execution plan