Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Spatial Question
Hi again..
I've copy/pasted your example and the query returned quickly (10g on
windows)...
Anonymous wrote:
> 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 23 2004 - 17:27:59 CST