Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to optimize massive insert on a table with spatial index ?

646920Jun 25 2008 — edited Oct 6 2009
Hello,

I need to implement a load process for saving up to 20 000 points per minutes in Oracle 10G R2.
These points represents car locations tracked by GPS and I need to store at least all position from the past 12 hours.
My problem is that the spatial index is very costly during insert (For the moment I do only insertion).

My several tries for the insertion by :
- Java and PreparedStatement.executeBatch
- Java and generation a SQLLoader file
- Java and insertion on view with a trigger "instead of"
give me the same results... (not so good)

For the moment, I work on : DROP INDEX, INSERT, CREATE INDEX phases.
But is there a way to only DISABLE INDEX and REBUILD INDEX only for inserted rows ?

I used the APPEND option for insertion :
INSERT /*+ APPEND */ INTO MY_TABLE (ID, LOCATION) VALUES (?, MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(?, ?, NULL), NULL, NULL))

My spatial index is created with the following options :
'sdo_indx_dims=2,layer_gtype=point'

Is there a way to optimize these heavy load ???
What about the PARALLEL option and how does it work ? (Not so clear for me regarding the documentation... I am not a DBA)

Thanks in advanced
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 3 2009
Added on Jun 25 2008
5 comments
5,712 views