FAQ: Why is the St_spatial_index Index Preserved When an Error is Encountered Attempting t

Answer

If the creation of the st_geometry index fails for any underlying DBMS error, the index still exists but is marked as invalid. In order to create the index after the error, the existing invalid index must first be dropped.

The following example demonstrates creating the sewers_shp_idx index on the sewers table's shape attribute, and it failing because the spatial reference specified, st_srid=999, does not exist.

Code:
SQL> CREATE INDEX sewers_shp_idx ON sewers (shape)
  2  INDEXTYPE IS sde.st_spatial_index
  3  PARAMETERS ('st_grids=100 st_srid=999');
CREATE INDEX sewers_shp_idx ON sewers (shape)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20083: Parameter ST_SRID 999 does not exist in ST_SPATIAL_REFERENCES table.
ORA-06512: at "SDE.SPX_UTIL", line 712
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 941

After the error one can verify the index does exist, but the index's operational status is marked failed. This indicates that the index is unusable.

Code:
SQL> SELECT index_name, domidx_opstatus
  2  FROM user_indexes
  3  WHERE index_name = 'SEWERS_SHP_IDX';

INDEX_NAME                     DOMIDX
------------------------------ ------
SEWERS_SHP_IDX                 FAILED

To fix the problem, drop the existing index and create the index again, fixing the problem that caused the index to fail on the previous attempt.

Code:
SQL> DROP INDEX sewers_shp_idx;

Index dropped.

SQL> CREATE INDEX sewers_shp_idx ON sewers (shape)
  2  INDEXTYPE IS sde.st_spatial_index
  3  PARAMETERS ('st_grids=100 st_srid=9');

Index created.

This is a limitation in Oracle's extensibility for implementing a domain index. Oracle's extensibility does not provide the ability to drop the index if an error is encountered; therefore, it is not an issue in how the index was implemented with st_geometry and st_spatial_index.

Article ID:000009722

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Why is the st_spatial_index index preserved when an error is encountered attempting to create the index?

Answer

Discover more on this topic