update statement with unacceptable performance

[Juerg]

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

[Mark D Powell]

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

[joel garry]

No real idea, except to wonder if you can make a function based index
on sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)).

This OTN related blog post shows how to ask these kinds of questions,
in a way where figuring what to ask might answer it:
http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html

jg
--
@home.com is bogus.
http://allthingsd.com/20130610/oracles-mark-hurd-still-has-no-interest-in-being-ceo-of-dell/

[Charles Hooper]

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

My first thought is to give Oracle Database an easier math problem to solve before forcing it to calculate the Pythagorean theorem. Doing so should help eliminate a large number of rows before having to calculating the two squares and a square root. The concept is found at the end of this article:
http://hoopercharles.wordpress.com/2011/06/13/calculate-the-distance-between-two-latitudelongitude-points-using-plain-sql/

Assume that g.y1-p.y1 is 0 - then that means the only acceptable values for g.x1-p.x1 are greater than -0.0007 and less than 0.0007 - those would always be the maximum extremes. Assume that g.x1-p.x1 is 0 - then that means the only acceptable values for g.y1-p.y1 are greater than -0.0007 and less than 0.0007. As such, you should be able to add the following to each of the WHERE clauses (you might also try switching the g and p alias in the following):
AND g.x1 BETWEEN (-0.0007 + p.x1) AND (0.0007 + p.x1)
AND g.y1 BETWEEN (-0.0007 + p.y1) AND (0.0007 + p.y1)

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

AND g.x1 BETWEEN (-0.0007 + p.x1) AND (0.0007 + p.x1)
AND g.y1 BETWEEN (-0.0007 + p.y1) AND (0.0007 + p.y1)

),
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

AND g.x1 BETWEEN (-0.0007 + p.x1) AND (0.0007 + p.x1)
AND g.y1 BETWEEN (-0.0007 + p.y1) AND (0.0007 + p.y1)

)
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

AND g.x1 BETWEEN (-0.0007 + p.x1) AND (0.0007 + p.x1)
AND g.y1 BETWEEN (-0.0007 + p.y1) AND (0.0007 + p.y1)
);

I wonder if an updateable inline view is acceptable here (see http://jonathanlewis.wordpress.com/2008/12/19/updatable-join-views/ for the concept) - that way the sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) would not need to be calculated so many times.

This check in your SQL statement is unnecessary, unless you are working with imaginary numbers (or you are verifying that the two sets of coordinates are not identical) - squaring a number always produces a positive number, so you are adding 2 positive numbers to see if the result is greater than 0 (a positive number):
sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) > 0

The above could simplified as:
AND (g.x1<>p.x1 OR g.y1<>p.y1)

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

[Kay Kanekowski]

Hallo Juerg,
do you know that you produce a cartesian product of 218.037 x 30.664 = 6.685.886.568 rows ? And you do it 3 times.

As Charles said, you don't need sqrt(power(..))>0.

So i create a temp table like this:

create temp_x1_y1
as
select g.x1,
g.y1,
p.x1 as new_x1,
p.y1 as new_y1
from point p,
geometry g
where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007
;

Ok, it is the cartesian product too, but only 1 time.

And with this i would try something like this
update geometry g
set
(x1, y1) = (select new_x1, new_y1 from temp_x1_y1 t where t.x1 = g.x1 and t.y1 = g.y1)
where exists (select 1 from temp_x1_y1 t where t.x1 = g.x1 and t.y1 = g.y1);

hth
bis denn
Kay

[Juerg]

Thanks a lot for all your answers.

I tried some modifcated statements, the best variation has a response time of 2 hours. That's acceptabe for runs during the night, but having some time left I will continue to optimize it.

kind regards
Juerg