Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Spatial Question

Spatial Question

From: Anonymous <new_open_close_at_yahoo.com>
Date: 16 Nov 2004 06:16:36 -0800
Message-ID: <83fb7fd5.0411160616.3faff725@posting.google.com>


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)



SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(1, 2, 3, 4))

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





SELECT STATEMENT
TABLE ACCESS                   BY INDEX ROWID                         
                                                                      
                                                                      
                                                                      

   GEOMETRY
DOMAIN INDEX Any help would be appreciated. Thanks. Received on Tue Nov 16 2004 - 08:16:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US