Bug: Upgrading to ArcSDE 9.3.1 invalidates all st_geometry spatial indexes in Oracle
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';
To detect all schemas in the Oracle instance that have a table with a st_geometry attribute and spatial index execute this query in SQL*Plus as the SDE user.
SELECT DISTINCT owner FROM sde.st_geometry_index
Use the above PL/SQL block in each schema to rebuild all the spatial indexes.