How to set appropriate spatial index for a feature class in Oracle?

Question

I wanted to ask is there any known method how to set spatial index for my feature class in Oracle. I am working with ESRI 10.1 and I have various feature classes covering the whole area of my country (Poland). Those feature classes represent forest, rivers, roads, cities etc. They are of course represented by different geometires so I have polygons, lines, points and annotations.

After loading all data into Oracle I wanted to recalcute spatial index to improve the speed in which the features are drawn on the map. Of course I could use the recalculate function in ArcCatalog for each feature class but I wanted to do it on my own. Maybe it will give better results?

I have found interesting solution at ESRI website, however I would like to ask how did other user deal with this problem:

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Guidelines_for_choosing_a_spatial_index_grid_size/006z0000002p000000/

• Mapperz: Do you use Oracle SQL Developer?
• Mapperz: You will need to drop an existing spatial index before re-creating it help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/…
• travis: Are you using SDO_Geometry (Oracle Spatial), or SDE.ST_Geometry (ESRI). You have this question tagged for Oracle-Spatial, but the index you build on an SDO_Geometry column is an RTree, and doesn't have a grid size. The spatial index on an SDE.ST_Geometry type requires a grid size. The link you provided is outlining how to choose an appropriate grid size.
• duszza: Yes I am using SDE.ST_Geometry and yes I use Oracle SQL Developer.
• duszza: Why do I need to drop an existing spatial index can't I just 'recalculate' it?

Answer 1

From personal experience, I find that having between 50-100 features per cell gives good performance. With datasets that have features that are a uniform size and distribution (something like regularly spaced points) this is fairly easy to achieve.

But really, I don't have to deal with many datasets that the grid size makes that big of a difference, so I leave the ESRI generated values. For the datasets that I tune extensively, I experiment to see if I can speed things up. Usually I do this through SQL Plus using the ST_EnvIntersects operator and auto trace, in a dev environment of course.

select index_name, table_name, srid, grid from sde.st_geometry_index where table_name = 'MYTABLE';
--alter the index to the new grid size, must keep the same srid.
alter index travis.myindex rebuild parameters ('st_grids=90,0,0 st_SRID=300002');
alter system flush buffer_cache;
alter system flush shared_pool;
set timing on
set autotrace trace exp stat 
select * from travis.mytablewhere sde.st_envintersects(shape, -90, -90, 90, 90) = 1;