Re: Spatial query tuning...

"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 indexes.

Jan

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message news:<cnfr05$dm$1_at_nntp.fujitsu-siemens.com>...
> Hi!
> I'm using oracle 9.2.0.4 on RH Linux.
> What we'd like to do is running some checks on PCBs. So, we load all
> pins and a bunch of testpins into spatial and then let oracle figure out
> which are too close to each other.
>
> these are the tables and indices:
> --Table for pins
> create table TPVSPAD_PADS
> (
> pinpad varchar(128),
> layer_no number(3),
> PAD mdsys.sdo_geometry,
> primary key (pinpad,layer_no)
> );
> insert into USER_SDO_GEOM_METADATA
> (table_name, column_name,diminfo,srid)
> values
> (
> 'TPVSPAD_PADS',
> 'PAD',
> MDSYS.SDO_DIM_ARRAY(
> MDSYS.SDO_DIM_ELEMENT('X',-1000,1000,0.00001),
> MDSYS.SDO_DIM_ELEMENT('Y',-1000,1000,0.00001)
> ),
> NULL
> );
> create index TPVSPAD_PADS_IND on TPVSPAD_PADS (PAD) indextype is mdsys.spatial_index;
> --table for testpoints
>
> create table TPVSPAD_TPS
> (
> pinpad varchar(128),
> layer_no number(3),
> PAD mdsys.sdo_geometry,
> primary key (pinpad,layer_no)
> );
> insert into USER_SDO_GEOM_METADATA
> (table_name, column_name,diminfo,srid)
> values
> (
> 'TPVSPAD_TPS',
> 'PAD',
> MDSYS.SDO_DIM_ARRAY(
> MDSYS.SDO_DIM_ELEMENT('X',-1000,1000,0.00001),
> MDSYS.SDO_DIM_ELEMENT('Y',-1000,1000,0.00001)
> ),
> NULL
> );
> create index TPVSPAD_TPS_IND on TPVSPAD_TPS (PAD) indextype is mdsys.spatial_index;
>
> Now we load TPVSPAD_TPS with about 800 testpoints and TPVSPAD_PADS with about
> 3300 pins. Testpoints are circles and pins can be pads or rectangles.
>
> The query is
> select
> SDO_GEOM.sdo_distance(dp.pad,tp.pad,0.00001),
> dp.pinpad,
> dp.layer_no,
> tp.pinpad,
> tp.layer_no
> from
> TPVSPAD_PADS dp,
> TPVSPAD_TPS tp
> where
> dp.layer_no=tp.layer_no
> and SDO_within_distance(dp.pad,tp.pad,'distance=1')='TRUE';
>
> and it has been running for one hour already, slowly returning results. I was expecting
> something in the range of less than one minute to be honest.
> Whan am I doing wrong?
>
> Lots of Greetings!
> Volker
Received on Thu Nov 18 2004 - 02:51:42 CST