This discussion is archived
4 Replies Latest reply: Nov 25, 2013 5:57 AM by Pleiadian RSS

spatial index and regular index

Pleiadian Journeyer
Currently Being Moderated

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 10.2.0.4.0

 

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?

 

Thanks!

 

Rob

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points