How To: Disassociate the st_contains operator from the domain index st_spatial_index in Oracle


Because Oracle operators are unable to use a domain index as the access path for the second argument in an operator's specification, the sde.st_contains operator should not be associated with the domain index st_spatial_index.

Instructions provided describe how to disassociate the st_contains operator from the domain index st_spatial_index in Oracle.

For additional information about the limitations of Oracle's extensible indexing and optimizer, see ESRI Knowledge Base article, "Poor query performance when using the st_contains operator in Oracle ".

Because the operator is associated with the spatial index, each time a SQL statement is parsed by the Oracle optimizer, it must derive a selectivity, a function cost, and index cost. These steps consume additional resources that are not required, knowing the domain index can never be used as the access path.


To disassociate the sde.st_contains operator from the domain, index st_spatial_index connect as the SDE user in SQL*Plus and execute the following command:

SQL> ALTER INDEXTYPE st_spatial_index
2 DROP st_contains (sde.st_geometry, sde.st_geometry);

Indextype altered.

Once the operator is no longer bound to the domain index, the optimizer, when parsing a SQL statement with the sde.st_contains operator, does not attempt to derive the operator's selectivity, function cost, and index cost when costing each execution plan.