How To: Disassociate the St_contains Operator from the Domain Index St_spatial

Summary

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.

Note:
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.

Procedure

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:

Code:
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.

Article ID: 000010501

Software:

Legacy Products

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Disassociate the st_contains operator from the domain index st_spatial_index in Oracle

Summary

Procedure

Receive notifications and find solutions for new or common issues

Discover more on this topic