# Re: update statement with unacceptable performance

From: Mark D Powell <Mark.Powell2_at_hp.com>

Date: Tue, 11 Jun 2013 10:08:01 -0700 (PDT)

Message-ID: <d772cfba-3c48-432f-bd6a-8274bce3e028_at_googlegroups.com>

On Tuesday, June 11, 2013 10:50:55 AM UTC-4, Juerg wrote:

Date: Tue, 11 Jun 2013 10:08:01 -0700 (PDT)

Message-ID: <d772cfba-3c48-432f-bd6a-8274bce3e028_at_googlegroups.com>

On Tuesday, June 11, 2013 10:50:55 AM UTC-4, Juerg wrote:

*> 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*Where is the explain plan showing how Oracle processed the statement?

How many rows consuming how many megabytes/gigabytes are there?

What full version of Oracle?

HTH -- Mark D Powell -- Received on Tue Jun 11 2013 - 19:08:01 CEST