Incorrect spatial index entries are created because of rounding conditions when generating gx, gy cell values in Oracle with a st_spatial_index

Description

Incorrect spatial index entries are created because of rounding conditions when generating gx, gy cell values.If using ArcGIS 9.2 Service Pack 5 with a spatial index created prior to service pack 5 under certain conditions, geometries existing near grid cell boundaries are not returned in the queries result set.The problem does not occur in all cases. Symptoms of the problem can include: 1. When the spatial index is used as the access path for spatial queries (panning and zooming in ArcMap or identify), features can fail to display at one map extent but draw at other map extents. 2. ArcGIS selections result in a selection set slightly smaller than the selection envelope used to perform the selection.

Cause

Additional st_spatial_index domain index entries are being generated because of rounding conditions when converting a variable type of NUMBER to an Oracle type INTEGER.

In sde.spx_util.compute_feat_grid_envp, a type of NUMBER is passed as an argument for generating a feature envelope's gx, gy cell values. When calculated, the outbound variable g_minx, g_miny, etc. is cast to an Oracle INTEGER. By default, Oracle rounds the input value to the closest INTEGER value, which in turn can generate a larger or smaller spatial index envelope than the actual feature. Under the conditions explained above, queries that use the spatial index as the access path might not return an accurate result set.

With ArcSDE 9.2 Service Pack 5 (SP5), a change was made to sde.spx_util.compute_feat_grid_envp to correct the rounding condition. The change impacts spatial indexes built prior to ArcGIS 9.2 Service Pack 5. It affects queries using spatial indexes created prior to SP5 and when inserting new geometries that are indexed.

This issue has no impact on SDEBINARY layers.

Workaround

Code:
DECLARE

  CURSOR st_idx IS
  SELECT index_name
    FROM user_indexes
   WHERE ityp_name = 'ST_SPATIAL_INDEX';

BEGIN

  FOR rebuild_idx IN st_idx LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX '||rebuild_idx.index_name||' REBUILD';
  END LOOP;

END;
/

Code:
SQL> ALTER INDEX <indexname> REBUILD;

Note:
Only the owner of the index or someone who has been granted the Oracle system privilege ALTER ANY INDEX may rebuild an index.