update statement with unacceptable performance

From: Juerg <scju_at_databaar.ch>
Date: Tue, 11 Jun 2013 07:50:55 -0700 (PDT)
Message-ID: <577404b9-29ee-4f36-9972-6ffb92d7c950_at_googlegroups.com>

hello

I have to update (snap) the coordinates of a first table (geometry) to the cordinates of a second table (point) if the difference is > 0 and < 0.0007 meter. I wrote the following statements:

SQL> select count(*) from geometry

COUNT(*)

---

218037
1 Zeilen ausgewählt.

SQL> select count(*) from point

COUNT(*)

---

30664
1 Zeilen ausgewählt.

update geometry g
set

   y1 = (

      select y1 from point p
      where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0 
        and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007

   ),
   x1 = (

      select x1 from point p
      where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0 
        and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007

   )
where exists (

   select 1 from point p
   where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0      and sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007 );

Well, the update took 9 hours (Oracle Enterprise Edition 10.2.0.3). In this data model oracle spatial option is not avaiable, I have to do it without this extension. There are indexes on y1 and x1 on both tables, but I suppose they are not used.

Any idea to solve this slowness? Thank in advance

kind regards

Juerg Received on Tue Jun 11 2013 - 16:50:55 CEST