Is This Content Helpful?
We're glad to know this article was helpful.
When upgrading to ArcSDE 9.3.1, tables with st_geometry attributes and a spatial index, st_spatial_index is marked as invalid.
When the index is invalid, spatial queries do not leverage the performance gains of accessing an index for generating the result set and the queries response time will degrade.
When an ArcSDE geodatabase is upgraded to 9.3.1, the st_domain_methods type used by the st_geometry attribute and st_spatial_index is altered. Because the type is altered, all dependent objects and spatial indexes are marked invalid by Oracle. Oracle marks the objects invalid because it is not sure if the relationship between the existing spatial indexes and the type being altered have broken any dependencies.
It then becomes the requirement of the type provider to validate the indexes or the end user to drop and create the index or rebuild the index.
To address invalid spatial indexes, the schema that owns the tables with the st_geometry attributes and spatial index must rebuild the index.
To repair the invalid indexes, the owner has two options: either drop and create the index or rebuild the index. Rebuilding an index is an easier solution as the owner does not need to recall what index parameters were initially used when the index was first created.
The following code provides a simple PL/SQL block that can be executed in SQL*Plus as the owner of the indexes to be rebuilt.
FOR index_cur IN (SELECT index_name FROM user_indexes WHERE ityp_name = 'ST_SPATIAL_INDEX' AND domidx_status <> 'VALID') LOOP
EXECUTE IMMEDIATE 'ALTER INDEX '||index_cur.index_name||' REBUILD';
SELECT DISTINCT owner FROM sde.st_geometry_index