Spatial Type for Oracle: Spatial index changes beginning at version 9.2 Service Pack 5 require rebuilding

Description

Under certain conditions, geometries existing near grid cell boundaries are not returned in a spatial query's result set.

The problem does not occur in all cases. Symptoms of the problem can include:

1. The spatial index is used to perform spatial queries such as, panning and zooming in ArcMap or identify, and features fail to draw at one extent but draw at other extents.

2. Selections produce a slightly smaller selection set than the selection envelope.

Cause

Starting with ArcSDE 9.2 Service Pack 5, a change was made to the Spatial Type for Oracle's spatial index (st_spatial_index) to correct a rounding condition when generating grid cell values. The rounding problem affected spatial indexes built prior to Service Pack 5.

The change in Service Pack 5 and all subsequent releases affects queries using existing spatial indexes and inserting new geometries into one of these spatial indexes.

Note:
This issue only impacts ST_GEOMETRY layers.

Workaround

• Look up the names of all the ST_GEOMETRY spatial indexes in a geodatabase by executing the following SQL command.
  
  

  Code:
  SQL> SELECT owner || '.' || index_name 
       FROM sde.st_geometry_index
       ORDER BY owner, index;

  
  
  
• Use the following SQL command to rebuild each index.
  
  

  Code:
  ALTER INDEX <indexname> REBUILD;

  
  

  Note:
  To rebuild an index, the user must be the owner of the index or have been granted the Oracle system privilege ALTER ANY INDEX.