2

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/

6
  • Do you use Oracle SQL Developer?
    – Mapperz
    Jul 11, 2013 at 21:27
  • You will need to drop an existing spatial index before re-creating it help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/…
    – Mapperz
    Jul 11, 2013 at 21:29
  • 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.
    – travis
    Jul 12, 2013 at 5:46
  • Yes I am using SDE.ST_Geometry and yes I use Oracle SQL Developer.
    – duszza
    Jul 12, 2013 at 7:57
  • Why do I need to drop an existing spatial index can't I just 'recalculate' it?
    – duszza
    Jul 12, 2013 at 7:57

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.