Re: Spatial Insert Performance

If you read "Oracle Spatial User's Guide and Reference"

there in a caption:

"Indexing of Spatial Data"

is a table "Choosing R-tree or Quadtree Indexing"

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96630/sdo_intro.htm#sthref91

where is written:

"If there is heavy update activity to the spatial column, an R-tree index may not
be a good choice.
Heavy update activity does not affect the performance of a quadtree index."

BTW, I found on metalink there is a list of bugs in 9.2.0.3 fixed in 9.2.0.4 related to SDO, maybe better to work with last patched version although the bugs are not related to inserts.

Jan

Peter Sylvester <peters_no_spam_please_at_mitre.org> wrote in message news:<ccmhju$psj$1_at_newslocal.mitre.org>...
> I'm running 9.2.0.3EE on W2K, and ran some simple performance tests.
>
> With a simple non-spatial table (id, lat, lon, all numbers), I can get
> inserts up around 12,000 records per second (using batch insert mode of
> Oracle's JDBC driver).
>
> I setup a similar table for use with spatial:
>
> CREATE TABLE test2 (
> id number not null,
> location MDSYS.SDO_GEOMETRY not null,
> constraint pk_test2 primary key (id)
> );
>
> When there is no spatial index, I can get about 10,000 inserts per
> second, slightly slower than the non-spatial table.
>
> After adding a spatial index, insert performance drops to 135
> inserts/second. Thats about 2 orders of magnitude different.
>
> Here is the index setup (RTREE Geodetic):
>
> INSERT INTO USER_SDO_GEOM_METADATA
> VALUES (
> 'test2',
> 'location',
> MDSYS.SDO_DIM_ARRAY(
> MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, 10),
> MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, 10)
> ),
> 8307 -- SRID for 'Lon/Lat WGS84 coordinate system
> );
> commit;
>
> CREATE INDEX test2_spatial_idx
> ON test2(location)
> INDEXTYPE IS MDSYS.SPATIAL_INDEX
> PARAMETERS('LAYER_GTYPE=POINT');
>
> I'm just inserting simple points, the SQL being used is below:
>
> insert into test2 (id, location)
> values (testseq.nextval,
> MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(?,?,NULL),null,null));
>
> (this is from the PreparedStatement definition).
>
> Any pointers/suggestions are appreciated!
> I know that dropping the index is a workaround, but I have an app that
> will expect to be doing inserts fairly continuously.
>
> thanks,
>
> --Peter
Received on Sat Jul 10 2004 - 07:28:51 CDT