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 -> Re: Spatial Question

Re: Spatial Question

From: Jan <janik_at_pobox.sk>
Date: 18 Nov 2004 00:17:41 -0800
Message-ID: <81511301.0411180017.7651f490@posting.google.com>


have you built the index after or before a bulk load of data? From the docs:

"A substantial number of insert and delete operations affecting an R-tree index may degrade the quality of the R-tree structure, which may adversely affect query performance."

Try to rebuild the index and let me see if that helped.

Jan

new_open_close_at_yahoo.com (Anonymous) wrote in message news:<83fb7fd5.0411160616.3faff725_at_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 Thu Nov 18 2004 - 02:17:41 CST

Original text of this message

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