Poor performance using st_geometry spatial relational operators when multiple grids are present

Description

When executing a SQL statement with a st_geometry spatial relational operator (st_intersects, st_within, etc.) and the table with the st_spatial_index contains multiple grid levels, the query's performance is slower because of the presence of multiple grid levels.

The following example demonstrates a query using the st_geometry operator st_intersects with two tables: parcels and waterways. The spatial index on the parcels shape attribute contains multiple grid levels and is used by the optimizer as the access path.

Code:
SELECT a.owner, a.address FROM 
parcels a, waterways b
WHERE b.name = 'South Fork Branch'
AND sde.st_intersects(a.shape,b.shape) = 1;

Cause

The cause of the problem is specific to an issue introduced in ArcGIS 9.3.1 Service Pack 1, where unnecessary looping impacts performance when multiple grid levels are present to delete Oracle PL/SQL collections in the termination of sde.spx_util.grid_search_execute.

Workaround