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):
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?
Partitioning was definitely the answer to our performance issues!
We created a partitioned table where the records are partitioned by attribute. We had to use range partitioning as list partitioning is not supported for local spatial indexes (as suggested). Next we created a local spatial index.
This speeds up the problem query roughly 50 times! Processing time has now dropped from 5 seconds to <0.1 second. Amazing
Thanks again yhu