Spatial query tuning...

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Wed, 17 Nov 2004 16:33:25 +0100
Message-ID: <cnfr05$dm$1@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 Wed Nov 17 2004 - 09:33:25 CST