4 Replies Latest reply on Nov 25, 2013 1:57 PM by Pleiadian

    spatial index and regular index


      Hi Spatial Specialists,


      I have a question about spatial index performance and how Oracle deals with queries that have a combined spatial and regular condition. We are on Oracle Database 10g Enterprise Edition Release


      Take a typical query like this one (not a real query but modeled on the type of queries that are being generated by ArcGIS):

      select objectid

      ,      attribute

      ,      geometry

      from   table

      where  mdsys.sdo_filter(geometry, mdsys.sdo_geometry(:gtype1, :srid1, null, :elem_info1, :ordinates1), 'querytype=window') = 'TRUE'

      and    attribute = value


      In our scenario we have a lot of rows in the table (about 2 million), but we only have a few rows (2000) where attribute = value.

      It seems that this is a scenario where performance is really bad. It takes up to 5 seconds for the query to return results and this is simply too slow for our requirements. There is a spatial index on the geometry and a normal index on the attribute field. The explain plan shows that both the spatial index and the attribute index are used.


      Of course we can break up the table in separate tables per type of object. This would make the spatial index much more efficient, but at the expense of our current simple and abstract datamodel.


      Do you have any suggestions on how we can improve performance without too much impact on our datamodel? Does Oracle for instance have an option where you can build a spatial index on a subset of the data in a table?