Hi-
Just a few pieces of info about me---I am not a DBA and I am new to Oracle..
I have a JAVA application that requires an insert rate of spatially enabled data at 1000-2000 inserts per second. The spatial data object could be a point, rectangle or a polygon of up to 20 points. I've used postgres(w/ PostGIS) in the past for this application and have gotten up to about 50% of the performance needed so I decided to try Oracle Spatial to see what it can give me.
My first results when using Oracle Spatial are very disappointing and I am hoping that this is due to my lack of knowledge
I've got a very small table (less than 10 rows) that I am inserting/updating different rows at a rate of 1000/sec . The update consists of changing the value of the SDO_GEOM column and also a Timestamp column. I take a crude measurement (sampling system clock) of insert time with an index defined on the SDO_GEOM column and I see insert times on the order 100 milliseconds. If I drop the index on the SDO_GEOM column the insert time drops to 4 milliseconds. I am not concerned(yet) about the absolute value of these numbers but the relative difference of performance with and without the index.
I've done some searching regarding spatial index performance for large numbers of inserts and a lot of what I find suggests to drop the SDO_GEOM index and then bulk load the data and reconstruct the index. This will not work for my application since the data is continuously being updated.
Here is a forum entry where someone reports a 50x performance reduction when using an index.
2401099
He got the drop index and bulk insert answer which presents the same problem as I stated above.
My question is, are my expectations for insert rate with spatial data and index reasonable or am I out the box to start?
regards
Karl