Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Spatial Question
Hello,
I'm new to Oracle spatial, and am having a pretty basic problem with 10G on Solaris. I have a question about performance.
I've created a table:
CREATE TABLE geometry (GEOMETRY SDO_GEOMETRY);
With the associated META data:
INSERT INTO USER_SDO_GEOM_METADATA values ('GEOMETRY', 'GEOMETRY', mdsys.SDO_dim_array(MDSYS.SDO_DIM_ELEMENT('X',-180,180,.001), MDSYS.SDO_DIM_ELEMENT('Y',-90,90,.001)), 8307); Built an index:
CREATE INDEX geom_idx ON geometry (geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX; Inserted a square:
insert into geometry values(MDSYS.SDO_GEOMETRY(2003,8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(1,2,3,4))); Can see the square:
select * from geometry where SDO_INSIDE(geometry, SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-5,-5,5,5)))= 'TRUE'; GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
The question is that it doesn't appear as if the index is being used properly for these "select" calls. The reason I mention this is that I'm trying to compare Oracle, Mysql, Postgres, etc.. I have a data set of several million polygons for a particular test. A portion of the test is to find a known set of several thousand polygons within a test polygon. I get back the result set from Mysql and Postgres in about 200-300 ms. When I try the same query in Oracle it takes about an hour.
I used "explain" on the select and got the following: SQL> select operation, options, object_name from plan_table;
OPERATION OPTIONS
OBJECT_NAME
TABLE ACCESS BY INDEX ROWID
GEOMETRY
DOMAIN INDEX
Any help would be appreciated. Thanks.
Received on Tue Nov 16 2004 - 08:16:36 CST