Slow searches in Oracle SDE 10.1

Have you noticed slow searches in Oracle SDE 10.1 when the search is based on OBJECTID, for example during a Productivity Suite COU data load?

This problem only affects ArcSDE v10.1 on Oracle using ST_GEOMETRY data type and it happens because an important index has not been built

To check if the index is missing you must first find the GEOM_ID of your data table. Substitute your data owner and table name into a SQL query like this.

select geom_id, owner, table_name from sde.st_geometry_columns where owner='OSDATA' and table_name like 'OSMMLINE';

Then substitute your GEOM_ID into a SQL query like this.

select index_name from user_indexes where table_name='S244_IDX$';

You should see 2 indexes returned with their names ending IX1 & IX2. If there is only 1 index returned then the solution is to either install the 10.1 SP1 Parcel Editing Performance Patch or to build the index manually using SQL.

To build the index manually substitute your GEOM_ID into a query like this.

create index S244$_IX2 on S244_IDX$(SP_ID) tablespace "INDEXES";

 For more information see this technical article.