English

Bug: 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

Fixing the problem requires rebuilding the spatial index of each ST_Geometry table after upgrading the geodatabase from an ArcSDE 9.2 Service Pack 4 or lower release geodatabase to an ArcSDE 9.2 Service Pack 5 or later release geodatabase. Be aware that index re-creation may be slow in ArcSDE 9.2 Service Pack 5 and ArcSDE 9.3; therefore, it is recommended to upgrade to ArcSDE 9.2 Service Pack 6, rebuild the indexes, and then upgrade to ArcSDE 9.3.

Use one of the two following methods:

- Use ArcCatalog to rebuild the spatial index on each feature class.

Or

- Use SQL to rebuild the indexes. The following bulleted points describe this process:

  • 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.

Related Information