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:
> 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
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